Capitolul 17 · 17.4
Căutarea după două condiții simultan
VLOOKUP și XLOOKUP caută după o singură valoare (consultați VLOOKUP generează #N/A: iată cum rezolvați problema și XLOOKUP: căutare spre stânga și cu un mesaj propriu). În practică, deseori căutați după două lucruri simultan: prețul unui buchet în mărimea L, tariful unui client într-un anumit an, stocul unui produs într-o anumită culoare. Pentru aceasta există trei metode bune, care funcționează toate cu un simplu Enter. O a patra metodă, pe care o întâlniți des pe internet, nu funcționează în Euro-Office fără un pas suplimentar.
Ca exemplu folosim lista de prețuri a florăriei Bloemisterij Het Zonnetje. Același produs apare de mai multe ori, în mărimi diferite:
| A | B | C | |
|---|---|---|---|
| 1 | Produs | Mărime | Preț |
| 2 | Boeket | S | 17,50 |
| 3 | Boeket | M | 24,50 |
| 4 | Boeket | L | 34,50 |
| 5 | Rouwstuk | M | 65,00 |
| 6 | Rouwstuk | L | 89,00 |
| 7 | Bruidsboeket | M | 79,50 |
Produsul căutat se află în F2 (Boeket), iar mărimea în G2 (L). Căutăm valoarea 34,50.
Metoda 1: XLOOKUP cu valori combinate
=XLOOKUP(F2&"|"&G2;A2:A7&"|"&B2:B7;C2:C7;"negăsit")
Caracterul & lipește textele unul de altul. F2&"|"&G2 devine "Boeket|L". A2:A7&"|"&B2:B7 creează o astfel de combinație și pentru fiecare rând din listă ("Boeket|S", "Boeket|M" și așa mai departe), iar XLOOKUP caută în aceasta. Bara verticală | dintre valori împiedică situația în care două combinații diferite produc din întâmplare același text. Dacă respectiva combinație nu există, de exemplu o coroană funerară în mărimea S, obțineți "negăsit".

Dacă vă uitați în bara de formule după ce ați tastat formula, veți vedea un @ în fața lui A2:A7 și B2:B7. Acesta este adăugat automat de Euro-Office; rezultatul este corect.
Metoda 2: FILTER pentru toate rezultatele
=FILTER(C2:C7;(A2:A7=F2)*(B2:B7=G2);"niciunul")
FILTER returnează nu doar primul rând, ci toate rândurile care îndeplinesc ambele condiții. Dacă o combinație apare de două ori, vedeți ambele prețuri unul sub altul. Asigurați-vă așadar că există spațiu liber sub formulă (consultați Liste care se actualizează singure cu FILTER, UNIQUE și SORT). Pentru „sau” în loc de „și” folosiți semnul plus: (A2:A7="Boeket")+(A2:A7="Rouwstuk").
Metoda 3: SUMIFS când combinația este unică
=SUMIFS(C2:C7;A2:A7;F2;B2:B7;G2)
Pare ciudat, deoarece nu doriți să adunați nimic. Însă dacă fiecare combinație apare o singură dată, suma este pur și simplu acel preț. Avantajul: SUMIFS este simplă, rapidă și este recunoscută de orice versiune de Excel. Dezavantajul: dacă respectiva combinație nu există, obțineți 0 (€ 0,00) în loc de un mesaj. Iar dacă combinația apare din greșeală de două ori, obțineți suma ambelor prețuri.
Ce găsiți pe internet: INDEX și MATCH cu o înmulțire
Multe site-uri despre Excel recomandă =INDEX(C2:C7;MATCH(1;(A2:A7=F2)*(B2:B7=G2);0)). În Euro-Office, această formulă returnează #N/A cu un simplu Enter, chiar și atunci când combinația există. Dacă o finalizați cu Ctrl+Shift+Enter, formula primește acolade {…} și funcționează. Acest pas se uită ușor, așa că vă recomandăm să folosiți XLOOKUP sau FILTER de mai sus.
Căutarea după rând și coloană
Dacă lista dvs. de prețuri este un tabel cu produsele unul sub altul și mărimile una lângă alta (o matrice), folosiți INDEX cu două funcții MATCH. Cu produsele în A11:A13, mărimile S, M și L în B10:D10 și prețurile în B11:D13:
=INDEX(B11:D13;MATCH(F2;A11:A13;0);MATCH(G2;B10:D10;0))
Prima funcție MATCH caută rândul produsului, a doua coloana mărimii. INDEX preia celula de la intersecție.
Ce metodă alegeți?
| Situație | Cea mai bună alegere |
|---|---|
| Un singur răspuns, cu un mesaj clar dacă nu există | XLOOKUP cu valori combinate |
| Fișierul trebuie să funcționeze și într-o versiune veche de Excel | SUMIFS |
| Pot exista mai multe rezultate | FILTER |
| Tabel de prețuri cu mărimile ca titluri de coloană | INDEX cu două funcții MATCH |
Capcane
- #N/A deși combinația există cu adevărat: adesea un spațiu după o valoare ("Boeket ") sau un număr stocat ca text. Consultați VLOOKUP generează #N/A: iată cum rezolvați problema.
- Zone de lungimi diferite: =XLOOKUP(F2;A2:A7;C2:C50) returnează #VALUE!. Asigurați-vă că toate zonele încep și se termină pe același rând.
- Coloanele întregi (A:A) în aceste formule încetinesc un fișier mare, deoarece Euro-Office combină atunci un milion de rânduri. Folosiți o zonă fixă, cum ar fi A2:A500. Consultați Cum faceți un calcul tabelar lent mai rapid.
- Un coleg care folosește Excel 2019 sau o versiune mai veche vede #NAME? la XLOOKUP și FILTER.
