Wyszukiwanie według wielu kryteriów za pomocą INDEX-MATCH

Czasami jedna kolumna nie identyfikuje jednoznacznie wiersza. Możesz potrzebować ceny produktu w konkretnym rozmiarze albo wynagrodzenia pracownika w określonym dziale.

W takiej sytuacji potrzebne jest wyszukiwanie wielokryterialne: dopasowanie jednocześnie do dwóch lub większej liczby kolumn, aby wskazać dokładnie jeden wiersz.

INDEX-MATCH elegancko rozwiązuje ten problem, łącząc warunki w jeden test dopasowania, bez konieczności dodawania kolumn pomocniczych.

Najprostszy sposób myślenia polega na połączeniu kolumn z kluczami w jedną kolumnę. Dodaj kolumnę pomocniczą, która sklei produkt i rozmiar, a następnie wykonaj zwykłe wyszukiwanie w tej kolumnie.

Przykładowa komórka pomocnicza może zawierać =A2&"|"&B2, tworząc wartość "Shirt|Large". Następnie użyj MATCH, aby wyszukać "Shirt|Large" w połączonej kolumnie.

To działa, ale zaśmieca arkusz. W kolejnych scenach pokazano, jak całkowicie pominąć kolumnę pomocniczą.

Najważniejszy trik polega na pomnożeniu dwóch testów warunków wewnątrz MATCH.

(A2:A10=G1) tworzy tablicę wartości TRUE/FALSE dla pierwszego kryterium. (B2:B10=G2) robi to samo dla drugiego. Ich pomnożenie, (A2:A10=G1)*(B2:B10=G2), daje 1 tylko tam, gdzie oba warunki mają wartość TRUE, a w pozostałych miejscach 0.

Funkcja MATCH wyszukuje następnie wartość 1, aby znaleźć wiersz spełniający oba warunki.

W arkuszach kalkulacyjnych TRUE zachowuje się jak 1, a FALSE jak 0. Mnożenie tych wartości naśladuje logiczne AND:

• 1 razy 1 = 1 (oba warunki spełnione)
• 1 razy 0 = 0
• 0 razy 1 = 0
• 0 razy 0 = 0

Wynik 1 powstaje więc tylko w wierszach, w których spełnione są oba kryteria. Każdy pozostały wiersz otrzymuje 0. Ta pojedyncza jedynka wskazuje szukany wiersz.

Teraz umieść pomnożoną tablicę wewnątrz MATCH i wyszukaj dokładną wartość 1.

MATCH(1, (A2:A10=G1)*(B2:B10=G2), 0) zwraca pozycję pierwszego wiersza, w którym oba warunki mają wartość TRUE.

Jeśli pasująca kombinacja znajduje się w czwartym wierszu danych, MATCH zwróci 4. To właśnie tej pozycji INDEX potrzebuje, aby pobrać wynik.

Przekaż wynik MATCH do funkcji INDEX działającej na kolumnie, z której chcesz pobrać wartość, na przykład na cenie w zakresie C2:C10.

Pełna formuła oznacza: z zakresu C2:C10 zwróć wartość z wiersza, w którym produkt jest równy G1 i rozmiar jest równy G2.

To prawdziwe wyszukiwanie wielokryterialne, bez kolumny pomocniczej i bez zmiany układu danych.

Ta formuła operuje na tablicach warunków. W nowoczesnym Excelu i Google Sheets wystarczy nacisnąć Enter, aby zadziałała.

W starszym Excelu (przed wprowadzeniem tablic dynamicznych) należy zatwierdzić ją jako formułę tablicową za pomocą Ctrl+Shift+Enter, co doda nawiasy klamrowe. Jeśli w starszej wersji Excela wynik jest nieprawidłowy lub pojawia się błąd, zwykle brakuje właśnie tego kroku.

Potrzebujesz trzech kryteriów? Po prostu pomnóż przez kolejny test. Załóżmy, że chcesz również dopasować kolor w kolumnie D do wartości wejściowej G3.

Każdy dodatkowy czynnik (range=criterion) jeszcze bardziej zawęża wynik. Tylko wiersze, w których wszystkie warunki mają wartość TRUE, zachowują iloczyn równy 1; dowolna wartość FALSE zmienia cały iloczyn na 0.

Ten wzorzec można rozszerzać o dowolną liczbę potrzebnych kolumn.

Dane: A = produkt, B = rozmiar, C = cena. Chcesz znaleźć cenę produktu "Shirt" w rozmiarze "Large".

• G1 = "Shirt", G2 = "Large".
• Tablice warunków zwracają 1 tylko w wierszu Shirt+Large, na przykład w wierszu 4.
• MATCH(1, ..., 0) zwraca 4.
• INDEX(C2:C10, 4) zwraca cenę z tego wiersza.

Zmień dowolną wartość wejściową, a formuła natychmiast ponownie znajdzie właściwy wiersz.

Pamiętaj o następujących kwestiach:

• Równe zakresy: każdy zakres warunku i kolumna INDEX muszą mieć taką samą wysokość.
• Brak dopasowania: jeśli żaden wiersz nie spełnia wszystkich kryteriów, MATCH zwróci #N/A. Owiń całość w IFERROR.
• Duplikaty: jeśli pasuje więcej niż jeden wiersz, MATCH zwróci tylko pierwszy. Kryteria powinny być na tyle szczegółowe, aby wskazywały unikatowy wiersz.

Jeśli pasować może wiele wierszy i zamiast pobierać jedną wartość woli Pan/Pani zsumować ich wartości, SUMPRODUCT jest przejrzystą alternatywą dla wprowadzanego tablicowo INDEX-MATCH.

Funkcja mnoży tablice warunków przez kolumnę wartości i dodaje wyniki, więc udział w sumie mają tylko wiersze spełniające oba kryteria. Nie trzeba używać Ctrl+Shift+Enter, ponieważ SUMPRODUCT natywnie obsługuje tablice.

Proszę używać INDEX-MATCH do pobierania jednej pasującej wartości, a SUMPRODUCT do agregowania wszystkich dopasowań.

Sprawdź swoją wiedzę na temat wyszukiwania według wielu kryteriów.

W przypadku wyszukiwania według wielu kryteriów za pomocą INDEX-MATCH:

• Proszę pomnożyć tablice warunków: (A=G1)*(B=G2) daje 1 tylko tam, gdzie wszystkie warunki są spełnione (logiczne AND).
• MATCH(1, ..., 0) znajduje pozycję tego wiersza.
• INDEX(returnCol, position) zwraca wartość.

W przypadku dodatkowych warunków proszę dodać kolejne czynniki *(range=criterion), zachować równą wysokość zakresów, zatwierdzić formułę za pomocą Ctrl+Shift+Enter w starszych wersjach programu Excel oraz zabezpieczyć ją funkcją IFERROR.