=X.WYSZUKAJ(1;(A2:A7=E2)*(B2:B7=F2);C2:C7) zwraca cenę z wiersza, w którym produkt to E2 i rozmiar to F2. Każde porównanie sprawdza każdy wiersz, ich pomnożenie daje 1 tylko tam, gdzie oba są spełnione, a X.WYSZUKAJ (XLOOKUP) szuka tej jedynki. Wymaga Excela 2021 albo Microsoft 365; wersja z INDEKS i PODAJ.POZYCJĘ poniżej działa w każdej wersji. Tabele pokazują formuły po angielsku, =XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7), ale możesz w nich wpisywać formuły także po polsku, ze średnikami.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Tea | Large | $3.00 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
=X.WYSZUKAJ(1;(A2:A7=E2)*(B2:B7=F2);C2:C7)Tea i Large spotykają się w wierszu 5, więc G2 zwraca $3.00. Wybierz Juice i Small: znowu $3.00, ale z innego wiersza. Dodaj czwarty argument na przypadek, gdy żaden wiersz nie pasuje do obu: =X.WYSZUKAJ(1;(A2:A7=E2)*(B2:B7=F2);C2:C7;"No such item").
Jak działają mnożone warunki
A2:A7=E2 porównuje każdy produkt z E2 i zwraca sześć wartości PRAWDA albo FAŁSZ. Pomnożenie dwóch takich list zamienia PRAWDA w 1, a FAŁSZ w 0, a wiersz daje 1 tylko wtedy, gdy ma 1 w obu. Kolumna D pokazuje tę listę, rozlaną z jednej formuły.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Both match | Product | Size | |
| 2 | Coffee | Small | $2.50 | 0 | Tea | Large | |
| 3 | Coffee | Large | $3.50 | 0 | |||
| 4 | Tea | Small | $2.00 | 0 | |||
| 5 | Tea | Large | $3.00 | 1 | |||
| 6 | Juice | Small | $3.00 | 0 | |||
| 7 | Juice | Large | $4.00 | 0 |
=(A2:A7=F2)*(B2:B7=G2)Tylko D5 wynosi 1. Zmień F2 albo G2, a jedynka się przesunie. Każde dodatkowe kryterium to jeszcze jedno *(zakres=wartość), a warunki nie muszą być równościami: *(C2:C7<3) dodaje „cena poniżej 3”. Każdy zakres musi obejmować te same wiersze (A2:A7, B2:B7, C2:C7): jeśli zwracany zakres ma inny rozmiar niż warunki, X.WYSZUKAJ zwraca #ARG! (po angielsku #VALUE!; tabele pokazują angielskie nazwy błędów).
INDEKS i PODAJ.POZYCJĘ z wieloma kryteriami
W Excelu 2019 i starszym PODAJ.POZYCJĘ może przeszukać tę samą tablicę w poszukiwaniu 1, a INDEKS zwraca cenę z tej pozycji.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Coffee | Large | $3.50 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
=INDEKS(C2:C7;PODAJ.POZYCJĘ(1;(A2:A7=E2)*(B2:B7=F2);0))Coffee i Large to pozycja 2 tablicy, a INDEKS zwraca $3.50. W polskim Excelu formuła to =INDEKS(C2:C7;PODAJ.POZYCJĘ(1;(A2:A7=E2)*(B2:B7=F2);0)). W Excelu 2019 i starszym to formuła tablicowa: zamiast Enter naciśnij Ctrl+Shift+Enter (Cmd+Shift+Enter na Macu), a Excel pokaże ją w nawiasach klamrowych. Samo Enter zwykle zwraca tam #N/D! albo #ARG!. W Excelu 365 wystarczy Enter. Postać z jednym kryterium jest na stronie INDEKS i PODAJ.POZYCJĘ.
Połącz kryteria w jeden klucz
Drugi sposób to zamiana dwóch kryteriów w jedno przez ich połączenie. WYSZUKAJ.PIONOWO potrzebuje połączonych wartości w kolumnie pomocniczej na początku tabeli (tę wersję pokazuje strona WYSZUKAJ.PIONOWO). X.WYSZUKAJ potrafi połączyć zakresy w samej formule, więc kolumna pomocnicza nie jest potrzebna.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Juice | Large | $4.00 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
=X.WYSZUKAJ(E2&"|"&F2;A2:A7&"|"&B2:B7;C2:C7)A2:A7&"|"&B2:B7 buduje sześć kluczy, takich jak Juice|Large, a X.WYSZUKAJ znajduje wśród nich Juice|Large: $4.00. W polskim Excelu formuła to =X.WYSZUKAJ(E2&"|"&F2;A2:A7&"|"&B2:B7;C2:C7). Między częściami wstaw separator. Bez niego „AB” i „C” łączą się w to samo „ABC” co „A” i „BC”, a wyszukiwanie może zwrócić niewłaściwy wiersz.
Jeśli szukana wartość jest liczbą, a każda kombinacja występuje raz, SUMA.WARUNKÓW (SUMIFS) daje tę samą odpowiedź bez żadnej tablicy: =SUMA.WARUNKÓW(C2:C7;A2:A7;E2;B2:B7;F2). Gdy nic nie pasuje, zwraca 0 zamiast błędu, co może ukryć literówkę.
Wszystkie dopasowania z FILTRUJ
X.WYSZUKAJ oraz INDEKS i PODAJ.POZYCJĘ zwracają pierwszy pasujący wiersz. Gdy pasuje kilka wierszy, a chcesz wszystkie, użyj funkcji FILTRUJ (FILTER) z tymi samymi warunkami.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Quarter | Sales | Matches | ||
| 2 | North | Laptop | Q1 | 120 | Q1 | 210 | |
| 3 | North | Phone | Q1 | 210 | Q2 | 225 | |
| 4 | South | Laptop | Q1 | 95 | Q3 | 240 | |
| 5 | North | Phone | Q2 | 225 | |||
| 6 | South | Phone | Q2 | 180 | |||
| 7 | North | Laptop | Q2 | 135 | |||
| 8 | North | Phone | Q3 | 240 |
=FILTRUJ(C2:D8;(A2:A8="North")*(B2:B8="Phone"))Trzy wiersze to North i Phone, więc F2 rozlewa ich kwartały i sprzedaż do F2:G4. W polskim Excelu formuła to =FILTRUJ(C2:D8;(A2:A8="North")*(B2:B8="Phone")). Zmień A3 na South, a lista skurczy się do dwóch. Jeśli żaden wiersz nie pasuje, FILTRUJ zwraca #OBL! (#CALC!); trzeci argument, na przykład "None", pokazuje zamiast tego tekst. Więcej opcji opisuje strona FILTRUJ.
Ćwiczenie: trzy kryteria
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Quarter | Sales | Region | South | |
| 2 | North | Laptop | Q1 | 120 | Product | Laptop | |
| 3 | North | Laptop | Q2 | 135 | Quarter | Q2 | |
| 4 | North | Phone | Q1 | 210 | Sales | ||
| 5 | South | Laptop | Q1 | 95 | |||
| 6 | South | Phone | Q2 | 180 | |||
| 7 | South | Laptop | Q2 | 110 | |||
| 8 | North | Phone | Q2 | 225 |
Twoja kolej: Zwróć w G4 sprzedaż dla regionu z G1, produktu z G2 i kwartału z G3.
Najczęściej zadawane pytania
Jak użyć X.WYSZUKAJ z wieloma kryteriami?
Pomnóż po jednym porównaniu na każde kryterium i szukaj 1: =X.WYSZUKAJ(1;(A2:A7=E2)*(B2:B7=F2);C2:C7). Każde porównanie daje PRAWDA albo FAŁSZ dla każdego wiersza, iloczyn wynosi 1 tylko tam, gdzie wszystkie dają PRAWDA, a X.WYSZUKAJ zwraca pierwszy taki wiersz.
Jak użyć INDEKS i PODAJ.POZYCJĘ z dwoma kryteriami?
Użyj tych samych mnożonych warunków w PODAJ.POZYCJĘ: =INDEKS(C2:C7;PODAJ.POZYCJĘ(1;(A2:A7=E2)*(B2:B7=F2);0)). W Excelu 2019 i starszym zatwierdź formułę przez Ctrl+Shift+Enter (Cmd+Shift+Enter na Macu).
Czy WYSZUKAJ.PIONOWO może użyć dwóch kryteriów?
Nie bezpośrednio. Dodaj na początku tabeli kolumnę pomocniczą, która łączy obie wartości, na przykład =A2&"|"&B2, a potem wyszukaj połączoną wartość: =WYSZUKAJ.PIONOWO(E2&"|"&F2;tabela_pomocnicza;kolumna;FAŁSZ).
Czy SUMA.WARUNKÓW może zastąpić wyszukiwanie z dwoma kryteriami?
Tak, gdy wartość jest liczbą, a każda kombinacja występuje raz: =SUMA.WARUNKÓW(C2:C7;A2:A7;E2;B2:B7;F2). Zwraca 0 zamiast #N/D!, gdy żaden wiersz nie pasuje, i sumuje wartości, gdy kombinacja występuje dwa razy.
Jak wyszukiwać z kryteriami LUB?
Dodawaj warunki zamiast je mnożyć: (A2:A7="Tea")+(A2:A7="Juice") daje 1 lub więcej tam, gdzie spełniony jest którykolwiek. Szukaj wartości większej od 0, na przykład =X.WYSZUKAJ(PRAWDA;((A2:A7="Tea")+(A2:A7="Juice"))>0;C2:C7).
