M365Con 2027 – bezpłatna konferencja online · 11–22 Jan 2027 · 120 sessions m365con.net

Blog · Mirko Peters

INDEX MATCH w Excelu: formuła, przykłady i typowe błędy

By · · 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]).

  • tablica to zakres, z którego pobierana jest wartość.
  • nr_wiersza to numer wiersza wewnątrz tego zakresu.
  • nr_kolumny to 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:

  • 0 znajduje 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.
  • -1 znajduje 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ł.

  1. Kliknij komórkę, w której ma pojawić się wynik, na przykład G2.
  2. Wpisz =INDEX(C2:C100,, aby wskazać, skąd pochodzi odpowiedź.
  3. Dodaj MATCH(F2,A2:A100,0)), aby wskazać, gdzie szukać identyfikatora.
  4. 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 0 w 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

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.

Entities in this article

Tematy tego artykułu:

Microsoft Excel – Narzędzie analityczne i BI firmy Microsoft

Related articles

Więcej artykułów (temat: Microsoft Excel):

RSS