INDEX MATCH: Lepsza metoda wyszukiwania

Jeśli używasz funkcji VLOOKUP do każdego zadania wyszukiwania w Excelu, nie jesteś osamotniony — to jedna z najbardziej rozpoznawalnych funkcji w świecie arkuszy kalkulacyjnych. Jednak doświadczeni użytkownicy Excela niemal powszechnie przechodzą na INDEX MATCH — połączenie dwóch funkcji, które jest bardziej elastyczne, bardziej niezawodne i potrafi rozwiązywać problemy, z którymi VLOOKUP po prostu sobie nie radzi. W tym artykule wyjaśniamy dokładnie dlaczego — z prawdziwą składnią, opracowanymi przykładami i praktycznym przewodnikiem, który możesz od razu zastosować.

Czym są INDEX i MATCH?

Zanim połączymy te funkcje, warto zrozumieć każdą z nich osobno.

Funkcja INDEX

INDEX zwraca wartość komórki znajdującej się na określonej pozycji w zakresie lub tablicy.

=INDEX(array, row_num, [col_num])

• array — zakres komórek, w którym szukamy
• row_num — pozycja wiersza w tym zakresie
• col_num — (opcjonalnie) pozycja kolumny w tym zakresie

Na przykład =INDEX(A1:A10, 3) zwraca wartość znajdującą się w trzecim wierszu kolumny A, od wiersza 1 do wiersza 10.

Funkcja MATCH

MATCH wyszukuje wartość w zakresie i zwraca jej numer pozycji — nie samą wartość, lecz liczbę wskazującą, gdzie się ona znajduje.

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value — wartość, której szukasz
• lookup_array — pojedynczy wiersz lub kolumna, w której odbywa się wyszukiwanie
• match_type — użyj 0 dla dokładnego dopasowania (najczęściej stosowane), 1 dla wartości mniejszej lub równej, -1 dla wartości większej lub równej

Na przykład, jeśli A1:A5 zawiera {Apple, Banana, Cherry, Date, Fig}, to =MATCH("Cherry", A1:A5, 0) zwraca 3, ponieważ Cherry jest trzecim elementem.

Łączenie INDEX i MATCH

Prawdziwa moc ujawnia się, gdy zagnieździsz funkcję MATCH wewnątrz INDEX. Zamiast wpisywać numer wiersza na stałe, pozwalasz funkcji MATCH obliczać go dynamicznie:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

To polecenie mówi Excelowi: „Znajdź pozycję szukanej wartości w zakresie wyszukiwania, a następnie zwróć odpowiadającą jej wartość z zakresu zwracanego." Oba zakresy muszą mieć ten sam rozmiar i być ułożone w tym samym kierunku.

Praktyczny przewodnik

Wyobraź sobie tabelę inwentaryzacji produktów o następującej strukturze:

ID produktu	Nazwa produktu	Kategoria	Cena jednostkowa	Stan magazynowy
P-101	Wireless Mouse	Electronics	$29.99	142
P-102	USB-C Hub	Electronics	$49.99	87
P-103	Desk Lamp	Office	$34.99	55
P-104	Notebook A5	Stationery	$8.99	310
P-105	Ergonomic Chair	Furniture	$299.00	12

Dane znajdują się w zakresie A2:E6, a nagłówki w wierszu 1. Chcesz wyszukać cenę jednostkową produktu, którego ID jest wpisany w komórce H2.

Z użyciem INDEX MATCH formuła w H3 będzie wyglądała następująco:

=INDEX($D$2:$D$6, MATCH(H2, $A$2:$A$6, 0))

Krok po kroku:

1. MATCH przeszukuje kolumnę A (ID produktu) w poszukiwaniu wartości z H2.
2. Zwraca pozycję wiersza — na przykład 3, jeśli H2 zawiera „P-103".
3. INDEX zwraca wartość z wiersza 3 kolumny D (Cena jednostkowa), czyli $34.99.

Zwróć uwagę na użycie bezwzględnych odwołań do komórek ze znakami dolara. Zablokowanie zakresów zapewnia poprawne działanie formuły po skopiowaniu jej do innych komórek.

Dlaczego INDEX MATCH jest lepsze niż VLOOKUP

Jeśli znasz już VLOOKUP z naszego kompletnego przewodnika po VLOOKUP, rozumiesz jego mocne strony. Ma jednak dobrze znane ograniczenia, które INDEX MATCH rozwiązuje w elegancki sposób.

1. Wyszukiwanie w lewo — VLOOKUP nie potrafi, INDEX MATCH tak

VLOOKUP przeszukuje wyłącznie skrajną lewą kolumnę tabeli i zwraca wartość znajdującą się na prawo. Jeśli kolumna wyszukiwania jest na prawo od kolumny zwracanej, VLOOKUP zawodzi. INDEX MATCH nie ma takich ograniczeń — zakres zwracany i zakres wyszukiwania są całkowicie niezależne, więc możesz zwracać wartości z dowolnej kolumny, w tym z kolumn znajdujących się na lewo od kolumny wyszukiwania.

2. Zmiany numerów kolumn nie psują formuły

VLOOKUP używa zakodowanego na stałe numeru indeksu kolumny (np. trzecia kolumna). Wstawienie lub usunięcie kolumny sprawia, że ten numer staje się błędny i funkcja po cichu zwraca nieprawidłowe dane. Ponieważ INDEX MATCH odwołuje się do rzeczywistych zakresów, wstawianie kolumn nigdy nie psuje formuły.

3. Szybsze działanie na dużych zbiorach danych

VLOOKUP skanuje całą tablicę tabeli przy każdym obliczeniu. INDEX MATCH przetwarza tylko konkretną kolumnę wyszukiwania i konkretną kolumnę zwracaną, co jest mierzalnie szybsze w skoroszytach z dziesiątkami tysięcy wierszy.

4. Wyszukiwanie dwukierunkowe (2D)

Możesz zagnieździć dwie funkcje MATCH — jedną dla wiersza, drugą dla kolumny — aby utworzyć wyszukiwanie dwuwymiarowe, którego VLOOKUP nie jest w stanie odtworzyć bez formuł pomocniczych:

=INDEX(B2:E6, MATCH(H2, A2:A6, 0), MATCH(H3, B1:E1, 0))

Tutaj MATCH(H2, A2:A6, 0) znajduje właściwy wiersz, a MATCH(H3, B1:E1, 0) — właściwą kolumnę. Zmiana którejkolwiek komórki wejściowej powoduje natychmiastowe dostosowanie formuły. Jest to szczególnie przydatne w pulpitach nawigacyjnych sprzedaży, gdzie trzeba pobierać mierniki z wielu wymiarów.

Obsługa błędów w elegancki sposób

Gdy nie zostanie znalezione dopasowanie, MATCH zwraca błąd #N/D. Opakuj całą formułę INDEX MATCH w funkcję IFERROR, aby wyświetlić zamiast tego czytelny komunikat:

=IFERROR(INDEX($D$2:$D$6, MATCH(H2, $A$2:$A$6, 0)), "Product not found")

Jest to szczególnie ważne w udostępnianych skoroszytach lub szablonach, w których użytkownicy końcowi wpisują wartości wyszukiwania — czysta obsługa błędów zapobiega zamieszaniu i frustracji. Połącz to z weryfikacją danych w komórce wejściowej, aby ograniczyć wpisy do prawidłowej listy, a otrzymasz niezawodne narzędzie wyszukiwania odporne na błędy użytkownika.

Wyszukiwanie według wielu kryteriów z INDEX MATCH

Jednym z najczęściej żądanych scenariuszy wyszukiwania jest dopasowanie według więcej niż jednego warunku. Załóżmy, że chcesz znaleźć cenę jednostkową tam, gdzie Kategoria to „Electronics" ORAZ Stan magazynowy jest mniejszy niż 100. Można to osiągnąć za pomocą tablicowej wersji INDEX MATCH.

=INDEX($D$2:$D$6, MATCH(1, ($C$2:$C$6="Electronics")*($E$2:$E$6<100), 0))

W starszych wersjach Excela (przed 365) naciśnij Ctrl + Shift + Enter, aby wprowadzić ją jako formułę tablicową — Excel otoczy ją nawiasami klamrowymi {}. W Excelu 365 i Excelu 2021 tablice dynamiczne obsługują to automatycznie, więc wystarczy zwykły klawisz Enter.

Jak to działa: każdy warunek tworzy tablicę wartości PRAWDA/FAŁSZ (1 i 0). Mnożenie ich razem tworzy nową tablicę, w której 1 pojawia się tylko tam, gdzie oba warunki są spełnione. MATCH znajdzie wtedy pierwszą jedynkę, a INDEX zwróci odpowiadającą jej cenę.

INDEX MATCH a XLOOKUP

Excel 365 wprowadził funkcję XLOOKUP, która upraszcza wiele zadań wyszukiwania za pomocą jednej funkcji. XLOOKUP doskonale sprawdza się w prostych wyszukiwaniach i obsługuje natywnie wyszukiwanie w lewo. Jednak INDEX MATCH pozostaje aktualnym rozwiązaniem z kilku powodów:

• Działa we wszystkich wersjach Excela, w tym Excel 2010, 2013, 2016 i 2019 — wszędzie tam, gdzie możesz udostępniać skoroszyty.
• Wzorzec wyszukiwania dwuwymiarowego z zagnieżdżonymi funkcjami MATCH jest nadal bardziej zwięzły niż alternatywy z XLOOKUP.
• Wiele profesjonalnych modeli finansowych i szablonów audytowych jest zbudowanych na INDEX MATCH, dlatego ich czytanie i utrzymanie wymaga znajomości tego połączenia.

Znajomość INDEX MATCH jest również podstawą przy bardziej zaawansowanych zadaniach, takich jak tworzenie dynamicznych pulpitów nawigacyjnych w Excelu, gdzie formuły wyszukiwania zasilają wykresy i tabele podsumowań aktualizowane automatycznie.

Szybkie wskazówki dla skutecznego używania INDEX MATCH

• Zawsze używaj 0 jako match_type dla dokładnych dopasowań, chyba że masz konkretny powód, aby stosować dopasowanie przybliżone.
• Blokuj zakresy za pomocą odwołań bezwzględnych ($), aby formuły były poprawnie kopiowane do innych wierszy lub kolumn.
• Utrzymuj zakresy wyszukiwania i zakresy zwracane tej samej długości — różne rozmiary powodują błędne wyniki.
• Nadawaj zakresom nazwy za pomocą Pola nazwy (po lewej stronie paska formuły), aby zwiększyć czytelność: =INDEX(UnitPrices, MATCH(H2, ProductIDs, 0)) jest znacznie łatwiejsze do sprawdzenia niż odwołania do komórek.
• Łącz z IF i SUMIFS dla warunkowych wyszukiwań — zapoznaj się z naszym przewodnikiem po SUMIF i SUMIFS, gdzie znajdziesz powiązane techniki formuł warunkowych.

Natychmiastowe tworzenie formuł INDEX MATCH

Jeśli stoisz przed złożonym wymaganiem wyszukiwania — wieloma kryteriami, niestandardowym układem tabeli lub odwołaniami między arkuszami — możesz opisać swoje potrzeby w zwykłym języku w GPTExcel i w ciągu kilku sekund otrzymać gotową do użycia formułę INDEX MATCH, zawierającą poprawne odwołania bezwzględne i obsługę błędów. Eliminuje to domysły i pozwala uzyskać działającą formułę bez ręcznego eksperymentowania.

Szersze techniki pisania formuł wspomagane przez sztuczną inteligencję opisuje artykuł o używaniu ChatGPT do pisania formuł Excel.

Często zadawane pytania

Czy INDEX MATCH jest zawsze lepsze niż VLOOKUP?

W zdecydowanej większości profesjonalnych zastosowań — tak. INDEX MATCH obsługuje wyszukiwanie w lewo, nie psuje się przy wstawianiu kolumn i obsługuje wyszukiwanie dwuwymiarowe oraz według wielu kryteriów. VLOOKUP jest prostsze do napisania w przypadku podstawowych wyszukiwań w prawo, lecz jego ograniczenia stają się uciążliwe wraz ze wzrostem złożoności danych.

Czy muszę naciskać Ctrl+Shift+Enter dla INDEX MATCH?

Tylko wtedy, gdy używasz wielokryterialnej, tablicowej wersji formuły w Excelu 2019 lub starszym. Standardowe formuły INDEX MATCH z jednym kryterium wprowadza się zwykłym klawiszem Enter we wszystkich wersjach Excela. W Excelu 365 i Excelu 2021 z tablicami dynamicznymi nawet wersje wielokryterialne nie wymagają skrótu tablicowego.

Co się dzieje, gdy w kolumnie wyszukiwania są zduplikowane wartości?

MATCH zawsze zwraca pozycję pierwszego znalezionego dopasowania. Jeśli kolumna wyszukiwania zawiera duplikaty i musisz pobrać dane dla każdego wystąpienia, rozważ użycie kolumny pomocniczej z połączonymi kluczami lub skorzystaj z Power Query — opisanego w naszym przewodniku po Power Query — aby przekształcić dane przed zastosowaniem wyszukiwania.

Czy INDEX MATCH może wyszukiwać wartości w różnych arkuszach?

Tak. Wystarczy uwzględnić nazwę arkusza w odwołaniach do zakresów. Na przykład: =INDEX(Sheet2!$D$2:$D$100, MATCH(H2, Sheet2!$A$2:$A$100, 0)). Formuła działa identycznie niezależnie od tego, czy zakresy znajdują się w tym samym arkuszu, czy w innym arkuszu tego samego skoroszytu.