INDEX MATCH w Excelu: formuła, przykłady i typowe błędy
By Mirko Peters · · Updated
INDEX MATCH w Excelu krok po kroku: formuła, działanie każdej funkcji, wyszukiwanie w lewo i dwukierunkowe, wiele kryteriów oraz typowe błędy, a także porównanie z funkcją VLOOKUP.
Automatically translated with AI from the original post. Read the English original
INDEX MATCH w Excelu to formuła wyszukiwania zbudowana z dwóch funkcji: =INDEX(zakres_zwracany; MATCH(wartość_wyszukiwania; zakres_wyszukiwania; 0)). Funkcja MATCH określa, na której pozycji na liście znajduje się wartość, a INDEX zwraca element z tej samej pozycji na innej liście. Ten artykuł jest przeznaczony dla użytkowników Excela, którzy piszą formuły VLOOKUP i napotykają jej ograniczenia, lub odziedziczyli skoroszyt pełen formuł INDEX MATCH i chcą je zrozumieć. Każdą funkcję omówimy osobno, potem pokażemy, jak współpracują, jak szukać w lewo, jak wykonać wyszukiwanie dwukierunkowe, jak używać dwóch kryteriów i co oznaczają błędy.
Najważniejsze wnioski
- INDEX MATCH to dwie funkcje w jednej formule: =INDEX(zakres_zwracany; MATCH(wartość_wyszukiwania; zakres_wyszukiwania; 0)). Funkcja MATCH znajduje pozycję, a INDEX zwraca element z tej pozycji.
- Jako trzeci argument funkcji MATCH użyj 0, aby uzyskać dokładne dopasowanie. Pozostawiona z domyślną wartością 1 funkcja MATCH zakłada, że dane są posortowane rosnąco.
- Ponieważ zakres wyszukiwania i zakres zwracany są od siebie niezależne, INDEX MATCH może szukać po lewej stronie kolumny wyszukiwania, czego VLOOKUP nie potrafi.
- Dla dwóch warunków można albo połączyć kolumny w kolumnie pomocniczej, albo użyć formuły, która mnoży dwa porównania wewnątrz funkcji MATCH.
- Opakuj formułę w funkcję IFNA, aby zastąpić #N/D komunikatem, a inne błędy nadal będą widoczne, dzięki czemu prawdziwe pomyłki nie zostaną ukryte.
Funkcję INDEX MATCH znajdziesz w wielu starszych skoroszytach, ponieważ działała we wszystkich wersjach Excela na długo przed pojawieniem się funkcji XLOOKUP. Nadal działa, i to jest główny powód, aby ją poznać. Szczegóły poniżej pochodzą ze stron Microsoftu dotyczących funkcji MATCH, INDEX oraz VLOOKUP.
Co robi funkcja INDEX
Funkcja INDEX zwraca wartość z pozycji wewnątrz zakresu. Strona Microsoftu podaje postać tablicową jako INDEX(tablica; nr_wiersza; [nr_kolumny]).
tablicato zakres, z którego pobierana jest wartość.nr_wierszato numer wiersza wewnątrz tego zakresu.nr_kolumnyto numer kolumny wewnątrz tego zakresu. Jeśli zakres ma tylko jedną kolumnę, można go pominąć.
Numeracja zaczyna się od komórki w lewym górnym rogu zakresu, a nie od arkusza. Przykład Microsoftu używa bloku, w którym w pierwszej kolumnie znajdują się Apples i Bananas, a w drugiej Lemons i Pears. Formuła =INDEX(A2:B3;2;2) zwraca Pears, a =INDEX(A2:B3;2;1) zwraca Bananas.
Warto zapamiętać dwie zasady z tej samej strony. Jeśli argument nr_wiersza lub nr_kolumny wskazuje poza zakres, funkcja INDEX zwraca błąd #ADR!. Jeśli ustawisz nr_wiersza lub nr_kolumny na 0, funkcja INDEX zwróci całą kolumnę lub cały wiersz. W aktualnym Microsoft 365 zatwierdzasz formułę zwykłym Enterem. W starszych wersjach wprowadza się ją jako formułę tablicową klawiszami Ctrl+Shift+Enter.
Sama funkcja INDEX wymaga, abyś już znał pozycję. Tu do gry wchodzi MATCH.
Co robi funkcja MATCH
Funkcja MATCH przeszukuje zakres i zwraca pozycję. Microsoft podaje jej składnię jako MATCH(wartość_wyszukiwania; tablica_wyszukiwania; [typ_dopasowania]). Nie zwraca samego elementu, tylko jego względną pozycję w zakresie.
Trzeci argument określa sposób dopasowania:
0znajduje pierwsze dokładne dopasowanie. Zakres może być w dowolnej kolejności. Tego będziesz używać najczęściej.1, które jest też wartością domyślną, jeśli pominiesz argument, znajduje największą wartość mniejszą lub równą wartości wyszukiwanej. Zakres musi być posortowany rosnąco.-1znajduje najmniejszą wartość większą lub równą wartości wyszukiwanej. Zakres musi być posortowany malejąco.
Przykłady Microsoftu używają wartości 25, 38, 40 i 41 w zakresie B2:B5. =MATCH(39,B2:B5,1) zwraca 2, czyli pozycję wartości 38, najbliższej mniejszej wartości. =MATCH(41,B2:B5,0) zwraca 4. A =MATCH(40,B2:B5,-1) zwraca #N/A, ponieważ te wartości nie są posortowane malejąco.
Na stronie znajdziesz też kilka dodatkowych zachowań funkcji: dopasowanie tekstu nie uwzględnia wielkości liter, więc „apple” i „APPLE” są traktowane jednakowo. Przy typie dopasowania 0 i tekstowej wartości wyszukiwanej ? dopasowuje dowolny pojedynczy znak, a * dowolny ciąg znaków, natomiast znak tyldy przed jednym z nich wymusza wyszukanie dosłownego znaku. Jeśli funkcja MATCH niczego nie znajdzie, zwraca #N/A.
Domyślna wartość 1 potrafi zmylić. Jeśli zapomnisz o trzecim argumencie, funkcja MATCH po cichu założy, że dane są posortowane, i może zwrócić błędną pozycję bez żadnego komunikatu o błędzie. Przy niemal każdym wyszukiwaniu identyfikatorów, nazw lub kodów wpisz 0.
Połączmy to w całość
Schemat jest zawsze ten sam: funkcja MATCH zagnieżdżona jest w INDEX i dostarcza numer wiersza.
=INDEX(zakres_zwrotu, MATCH(wartość_wyszukiwana, zakres_wyszukiwania, 0))
Załóżmy arkusz z identyfikatorami pracowników w A2:A100, nazwiskami w B2:B100 i działami w C2:C100. W komórce F2 wpisujesz identyfikator i chcesz poznać dział.
- Kliknij komórkę, w której ma pojawić się wynik, na przykład G2.
- Wpisz
=INDEX(C2:C100,, aby wskazać, skąd pochodzi odpowiedź. - Dodaj
MATCH(F2,A2:A100,0)), aby wskazać, gdzie szukać identyfikatora. - Naciśnij klawisz Enter.
Gotowa formuła to =INDEX(C2:C100,MATCH(F2,A2:A100,0)). Funkcja MATCH znajduje F2 w A2:A100 i zwraca na przykład 14. Funkcja INDEX zwraca wtedy 14. element z C2:C100.
Zachowaj te same rozmiary zakresów i ten sam wiersz początkowy. Jeśli MATCH przeszukuje A2:A100, a INDEX odczytuje C3:C101, pozycja wskazuje na inny wiersz i wynik jest po cichu błędny. Użyj tych samych wierszy początkowego i końcowego w obu funkcjach, a jeśli planujesz kopiować formułę w dół, zablokuj zakresy znakami dolara: $A$2:$A$100.
Wyszukiwanie w lewo i różnice względem VLOOKUP
Strona Microsoftu o funkcji VLOOKUP podaje składnię VLOOKUP(wartość_wyszukiwana, tabela, nr_indeksu_kolumny, [przeszukiwanie_przybliżone]). Wskazuje, że wartość wyszukiwana musi znajdować się w pierwszej kolumnie zakresu, a pominięcie argumentu range_lookup daje dopasowanie przybliżone, zakładające posortowane dane. Strona sugeruje też wypróbowanie funkcji XLOOKUP.
Oto to samo zadanie wykonane obiema metodami, przy identyfikatorach w kolumnie A i nazwiskach w kolumnie B:
=VLOOKUP(F2,A2:B100,2,FALSE)=INDEX(B2:B100,MATCH(F2,A2:A100,0))
INDEX MATCH ma dwie realne zalety. Po pierwsze, potrafi szukać w lewo. Jeśli nazwiska są w kolumnie B, a chcesz otrzymać identyfikator z kolumny A, wystarczy zamienić zakresy: =INDEX(A2:A100,MATCH(F2,B2:B100,0)). VLOOKUP tego nie potrafi, ponieważ jego wartość wyszukiwana musi znajdować się w pierwszej kolumnie. Po drugie, odwołuje się bezpośrednio do kolumny zwrotu, zamiast ją liczyć. W funkcji VLOOKUP cyfra 2 w formule oznacza „druga kolumna zakresu”. Wstawienie kolumny wewnątrz zakresu sprawia, że ta liczba wskazuje na inną kolumnę. W przypadku INDEX MATCH odwołanie dostosowuje się jak każde inne odwołanie.
Często można też przeczytać, że INDEX MATCH jest szybszy. Strony Microsoftu takiej tezy nie potwierdzają, a ewentualna różnica ma znaczenie tylko w bardzo dużych skoroszytach, dlatego nie wybieraj tej metody ze względu na szybkość. Wybierz ją ze względu na elastyczność.
Wyszukiwanie dwukierunkowe
Wyszukiwanie dwukierunkowe znajduje wartość na przecięciu wiersza i kolumny, na przykład sprzedaż jednego produktu w jednym miesiącu. Użyj dwóch funkcji MATCH: jednej dla wiersza i jednej dla kolumny:
=INDEX(B2:M20,MATCH(P1,A2:A20,0),MATCH(Q1,B1:M1,0))
Tutaj zakres B2:M20 zawiera liczby, A2:A20 nazwy produktów, B1:M1 nagłówki miesięcy, a P1 i Q1 zawierają produkt i miesiąc, których szukasz. Pierwsze MATCH zwraca wiersz, drugie kolumnę, a INDEX zwraca liczbę znajdującą się na ich przecięciu. Zwróć uwagę, że zakres zwracany zaczyna się od B2, a nie od A1, dzięki czemu pozycje są zgodne z zakresami etykiet: oba zakresy MATCH zaczynają się również w tym samym wierszu lub kolumnie co liczby.
Dopasowanie według dwóch kryteriów
MATCH przyjmuje jedną wartość wyszukiwania. Jeśli rekord jest identyfikowany dopiero przez dwa pola, na przykład kategorię i status, masz do wyboru dwa sposoby.
Kolumna pomocnicza. Dodaj kolumnę łączącą oba pola, na przykład =A2&"|"&B2 w komórce D2, i skopiuj formułę w dół. Następnie wyszukaj połączony tekst:
=INDEX(C2:C100,MATCH(F2&"|"&G2,D2:D100,0))
Separator ma znaczenie. Bez niego „AB” i „C” oraz „A” i „BC” dałyby ten sam wynik „ABC”. Kolumna pomocnicza jest łatwa do odczytania i sprawdzenia, a działa we wszystkich wersjach.
Formuła tablicowa. Bez kolumny pomocniczej możesz pomnożyć dwa porównania:
=INDEX(C2:C100,MATCH(1,(A2:A100=F2)*(B2:B100=G2),0))
Każde porównanie daje wynik PRAWDA lub FAŁSZ. Mnożenie zamienia je na 1 tylko wtedy, gdy oba warunki są spełnione, a MATCH szuka pierwszej jedynki. Tej techniki nie ma na stronach Microsoft o INDEX i MATCH, więc przetestuj ją na niewielkim zakresie. W starszych wersjach programu Excel może być potrzebne wprowadzenie formuły kombinacją Ctrl+Shift+Enter, tak jak Microsoft opisuje dla wyników tablicowych funkcji INDEX. W aktualnym Microsoft 365 wystarczy Enter.
Symbole wieloznaczne i wartości najbliższe
Przy typie dopasowania 0 i tekście MATCH obsługuje symbole wieloznaczne. Formuła =INDEX(B2:B100,MATCH("Jan*",A2:A100,0)) zwraca element znajdujący się obok pierwszego wpisu, który zaczyna się od „Jan”. Jest to przydatne przy częściowych kodach. Zachowaj precyzyjny wzorzec, ponieważ MATCH zatrzymuje się na pierwszym trafieniu.
W przypadku progów, takich jak przedziały podatkowe czy widełki prowizyjne, celowo użyj domyślnego typu dopasowania 1, przy progach posortowanych rosnąco. Wpisz 0, 1000 i 5000 w komórkach A2:A4, a stawki w B2:B4. Formuła =INDEX(B2:B4,MATCH(E2,A2:A4,1)) zwróci stawkę dla największego progu, który nie przekracza wartości w E2. To jedno z nielicznych zadań, w których domyślne działanie na posortowanych danych jest właściwym wyborem.
Naprawianie błędów
- #N/A. MATCH niczego nie znalazł. Sprawdź literówki, spacje na końcu i liczby zapisane jako tekst, ponieważ „100” i 100 to różne wartości. Upewnij się, że typ dopasowania to 0, a typy dopasowania dla danych posortowanych (1 lub -1) faktycznie działają na posortowanych danych.
- #REF!. INDEX otrzymał żądanie wiersza lub kolumny spoza zakresu. Dzieje się tak również wtedy, gdy usuniesz wiersz lub kolumnę, z których korzysta formuła. Sprawdź, czy zakres MATCH i zakres INDEX mają ten sam rozmiar.
- Błędny wynik bez komunikatu o błędzie. Zwykle brakuje 0 w MATCH albo zakresy zaczynają się w różnych wierszach.
- Błędny wynik po skopiowaniu formuły w dół. Zakresy się przesunęły, ponieważ nie zostały zablokowane. Dodaj znaki
$do zakresów.
Aby zastąpić błąd komunikatem, funkcja IFNA firmy Microsoft zwraca wybraną przez Ciebie wartość, gdy formuła zwraca #N/A, a wszystkie inne błędy przepuszcza bez zmian:
=IFNA(INDEX(C2:C100,MATCH(F2,A2:A100,0)),"Nie znaleziono")
Przy wyszukiwaniu preferuj IFNA zamiast IFERROR. IFERROR ukrywa każdy błąd, w tym #REF! spowodowany uszkodzonym zakresem, więc rzeczywista usterka wygląda jak zwykłe „Nie znaleziono”. IFNA obejmuje wyłącznie przypadek, którego się spodziewasz. Microsoft podaje, że IFNA jest dostępna w programie Excel 2016 i nowszych.
INDEX MATCH czy XLOOKUP
Jeśli wszyscy, którzy otwierają Twój plik, mają aktualną wersję Microsoft 365, funkcja XLOOKUP wykona to samo zadanie jedną funkcją i z wbudowanym komunikatem o braku wyniku. Opisujemy ją w naszym przewodniku po XLOOKUP w programie Excel. Strona Microsoft o XLOOKUP nie jest jednak jednoznaczna co do Excela 2016 i 2019, a nie wymienia też Excela w przeglądarce. INDEX MATCH działa we wszystkich tych wersjach. Wybór jest więc praktyczny: używaj XLOOKUP w plikach przeznaczonych dla aktualnych wersji, a INDEX MATCH wtedy, gdy plik trafia do osób ze starszymi wersjami lub gdy utrzymujesz skoroszyt, który już z niego korzysta.
Częste błędy
- Pominięcie
0w MATCH i poleganie na wartości domyślnej. - Używanie w INDEX i MATCH zakresów o różnych rozmiarach lub różnych wierszach początkowych.
- Zapominanie o zablokowaniu zakresów znakami
$przed skopiowaniem formuły w dół. - Łączenie kryteriów bez separatora w kolumnie pomocniczej.
- Owijanie wszystkiego w IFERROR, przez co uszkodzone formuły wyglądają jak brakujące dane.
- Oczekujesz, że INDEX MATCH zwróci wszystkie dopasowania. Zwraca tylko pierwsze.
Powiązane tematy do zgłębienia
- XLOOKUP. Nowsza funkcja wyszukiwania z wbudowanym kierunkiem wyszukiwania i argumentem na wypadek braku wyniku.
- Kilka wyników naraz. INDEX MATCH zwraca tylko pierwsze dopasowanie. W wersjach, które ją mają, do zwrócenia wszystkich dopasowań służy funkcja FILTER.
- Tabele i nazwane zakresy. Odwołania strukturalne i nazwy ułatwiają czytanie wyszukiwań i pozwalają zakresowi rosnąć wraz z danymi.
- Wydajność na dużych arkuszach. Zamiast całych kolumn używaj ograniczonych zakresów i trzymaj je możliwie wąskie.
- Wyszukiwania rozróżniające wielkość liter. MATCH ignoruje wielkość liter, więc wyszukiwanie z uwzględnieniem wielkości liter wymaga innego podejścia. Przed użyciem jakiegokolwiek obejścia je przetestuj.
Następny krok
Przepisz jedną ze swoich formuł VLOOKUP jako INDEX MATCH i porównaj wynik na kilku znanych wartościach. Następnie spróbuj tej samej formuły z wyszukiwaniem w lewo. Gdy wykonasz oba kroki, będziesz wiedzieć, kiedy INDEX MATCH w Excelu jest lepszym narzędziem do danego zadania.
Źródła
- Funkcja MATCH (Microsoft Support)
- Funkcja INDEX (Microsoft Support)
- Funkcja VLOOKUP (Microsoft Support)
- Funkcja IFNA (Microsoft Support)
- Funkcja XLOOKUP (Microsoft Support)
FAQ
Jak działa INDEX MATCH w Excelu?
Funkcja MATCH przeszukuje zakres w poszukiwaniu wartości wyszukiwania i zwraca jej pozycję, na przykład 5 dla piątego elementu. Funkcja INDEX używa tej liczby, aby zwrócić element z tej samej pozycji w innym zakresie. Razem formuła =INDEX(C2:C100;MATCH(F2;A2:A100;0)) znajduje wartość F2 w kolumnie A i zwraca wartość z kolumny C.
Czy INDEX MATCH jest lepsze od VLOOKUP?
Jest bardziej elastyczne. VLOOKUP wymaga, aby wartość wyszukiwania znajdowała się w pierwszej kolumnie zakresu, a kolumny wskazuje numerem, natomiast INDEX MATCH korzysta z dwóch osobnych zakresów, dzięki czemu może szukać w lewo i nie zakłóca go wstawianie kolumn. Przy prostym wyszukiwaniu w prawo można użyć obu funkcji. XLOOKUP to trzecia opcja w nowszych wersjach.
Dlaczego INDEX MATCH zwraca #N/D?
Funkcja MATCH zwraca #N/D, gdy nie znajdzie dopasowania. Sprawdź literówki, dodatkowe spacje, liczby zapisane jako tekst oraz typ dopasowania. Użyj 0, aby uzyskać dokładne dopasowanie. Przy domyślnej wartości 1 zakres wyszukiwania musi być posortowany rosnąco, a przy -1 malejąco.