Łączenie INDEX i MATCH

Znają już Państwo dwie części wyszukiwania. Funkcja MATCH znajduje, gdzie znajduje się wartość, a funkcja INDEX zwraca wartość z określonej pozycji.

Po połączeniu otrzymujemy kompletne wyszukiwanie: MATCH wskazuje wiersz, a następnie INDEX pobiera dane z tego wiersza i wybranej kolumny.

Gdy zobaczą Państwo ten schemat, okaże się on prosty: należy umieścić funkcję MATCH wewnątrz funkcji INDEX, w miejscu, w którym zwykle podaje się numer wiersza.

Oto postać, której będą Państwo używać wielokrotnie:

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

Należy czytać ją od środka. Najpierw wykonuje się MATCH i zwraca numer pozycji. Następnie ta liczba staje się wartością row_num dla funkcji INDEX, która zwraca wartość z zakresu zwracanych danych.

Zakres zwracanych danych i zakres wyszukiwania zwykle mają taką samą liczbę wierszy, dzięki czemu pozycja w jednym z nich odpowiada pozycji w drugim.

Wyobraźmy sobie tabelę, w której kolumna A zawiera nazwy produktów, a kolumna C ceny. Chcą Państwo znaleźć cenę produktu „Cherry”.

Najpierw funkcja MATCH znajduje Cherry: =MATCH("Cherry", A2:A20, 0) zwraca na przykład 3.

Następnie funkcja INDEX używa tej wartości 3: =INDEX(C2:C20, 3) zwraca cenę z 3. wiersza kolumny C.

Po zagnieżdżeniu obu funkcji otrzymujemy wynik za jednym razem: =INDEX(C2:C20, MATCH("Cherry", A2:A20, 0)).

Wpisanie na stałe wartości „Cherry” jest odpowiednie podczas nauki, ale w rzeczywistych formułach odwołuje się zamiast tego do komórki. Należy umieścić wyszukiwany tekst w komórce E1 i użyć do niej odwołania.

=INDEX(C2:C20, MATCH(E1, A2:A20, 0))

Teraz dowolny produkt wpisany do komórki E1 natychmiast zwróci jego cenę. Po wpisaniu Banana otrzymają Państwo cenę produktu Banana, a po wpisaniu Date wynik zostanie zaktualizowany.

Jedna formuła staje się wielokrotnie używanym narzędziem wyszukiwania, sterowanym wyłącznie przez komórkę wejściową.

Oto trik, który wyróżnia INDEX-MATCH. Kolumna wyszukiwania i kolumna zwracana są niezależne, więc zwracana wartość może znajdować się na lewo od wyszukiwanej wartości.

Załóżmy, że ceny znajdują się w kolumnie A, a nazwy produktów w kolumnie C. Aby znaleźć cenę produktu na podstawie jego nazwy, należy użyć formuły =INDEX(A2:A20, MATCH(E1, C2:C20, 0)).

Wyszukiwanie odbywa się w kolumnie C, ale wartość jest zwracana z kolumny A. Funkcja VLOOKUP nie potrafi tego zrobić bez dodatkowych zabiegów.

Zakres zwracanych wartości decyduje o tym, co zostanie zwrócone. Wyszukując za pomocą tego samego klucza, można pobrać dowolną kolumnę, zmieniając tylko zakres funkcji INDEX.

Aby znaleźć adres e-mail klienta: =INDEX(D2:D50, MATCH(E1, A2:A50, 0)).

Aby zamiast tego znaleźć miasto tego samego klienta: =INDEX(F2:F50, MATCH(E1, A2:A50, 0)).

Część MATCH pozostaje identyczna; zmienia się tylko zakres funkcji INDEX, aby wybrać inną wartość.

Można również podać numer kolumny funkcji INDEX, znajdując go za pomocą drugiej funkcji MATCH. Pozwala to wskazać wartość na przecięciu wiersza i kolumny.

=INDEX(B2:E10, MATCH(G1, A2:A10, 0), MATCH(G2, B1:E1, 0))

Pierwsza funkcja MATCH znajduje wiersz na podstawie etykiet w kolumnie A, a druga znajduje kolumnę na podstawie nagłówków w wierszu 1. Funkcja INDEX zwraca komórkę znajdującą się na ich przecięciu. Ten zaawansowany wzorzec zostanie dokładnie omówiony w dalszej części.

Aby pozycje się zgadzały, zakres wyszukiwania i zakres zwracanych wartości muszą zaczynać się w tym samym wierszu i mieć tę samą wysokość.

Jeśli funkcja MATCH przeszukuje zakres A2:A20 (19 wierszy), ale funkcja INDEX zwraca wartości z zakresu C2:C19 (18 wierszy), pozycje się rozjeżdżają i otrzymuje się nieprawidłową wartość.

Niezawodna praktyka to używanie dokładnie tego samego zakresu wierszy w obu przypadkach, na przykład A2:A20 i C2:C20. Odwołania do całych kolumn, takie jak A:A i C:C, również automatycznie zachowują zgodność.

Jeśli funkcja MATCH nie może znaleźć wyszukiwanej wartości, zwraca #N/A, a cała formuła INDEX-MATCH wyświetla ten błąd. Można ją opakować funkcją IFNA, aby uzyskać czytelny wynik zastępczy.

=IFNA(INDEX(C2:C20, MATCH(E1, A2:A20, 0)), "Not found")

Brakujący produkt wyświetli teraz tekst "Not found" zamiast niepokojącego błędu. Funkcja IFERROR również działa, ale IFNA obsługuje tylko przypadek braku dopasowania i pozwala wyświetlać pozostałe błędy.

Połączmy wszystkie elementy. Dostępna jest tabela pracowników: identyfikatory znajdują się w kolumnie A, nazwiska w B, działy w C, a wynagrodzenia w D. Użytkownik wpisuje identyfikator do komórki G1.

Aby zwrócić dział tego pracownika: =INDEX(C2:C200, MATCH(G1, A2:A200, 0)).

Aby zamiast tego zwrócić jego wynagrodzenie, należy zmienić zakres funkcji INDEX na D2:D200. Logika wyszukiwania się nie zmienia, zmienia się tylko kolumna, z której odczytywana jest wartość. To podstawowe narzędzie codziennej pracy z dynamicznym wyszukiwaniem.

Gdy formuła wygląda onieśmielająco, należy analizować ją tak, jak robi to arkusz kalkulacyjny — od najbardziej wewnętrznej funkcji na zewnątrz.

W przypadku formuły =INDEX(C2:C20, MATCH(E1, A2:A20, 0)) najpierw należy odczytać MATCH(E1, A2:A20, 0), wyobrazić sobie, że zwraca liczbę, na przykład 5, a następnie zastąpić nią tę część, otrzymując =INDEX(C2:C20, 5).

Nagle formuła oznacza po prostu „zwróć piątą cenę”. Ten nawyk ułatwia debugowanie każdej zagnieżdżonej funkcji wyszukiwania.

Sprawdź, czy rozumiesz, jak te dwie funkcje współdziałają.

Połączono dwie funkcje w elastyczny mechanizm wyszukiwania:

• Wzorzec: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
• MATCH znajduje pozycję wiersza, a INDEX zwraca wartość z tej pozycji
• Kolumny wyszukiwania i zwracanych wartości są niezależne, więc można wyszukiwać równie łatwo w lewo, jak i w prawo
• Oba zakresy powinny mieć tę samą wysokość, a funkcję należy opakować w IFNA, aby czytelnie obsługiwać błędy

Następnie zobacz, dlaczego to podejście często przewyższa funkcję VLOOKUP.