Wyszukiwanie dwukierunkowe za pomocą INDEX-MATCH-MATCH

Wyobraźmy sobie siatkę miesięcznej sprzedaży, w której regiony znajdują się po lewej stronie, a miesiące są rozmieszczone wzdłuż górnego wiersza. Chcą Państwo znaleźć wartość w miejscu przecięcia wybranego regionu i wybranego miesiąca.

Standardowe wyszukiwanie znajduje wartość, przeszukując tylko jeden kierunek. Wyszukiwanie dwukierunkowe przeszukuje oba kierunki jednocześnie: znajduje właściwy wiersz i właściwą kolumnę, a następnie zwraca wartość znajdującą się na ich przecięciu.

Klasycznym narzędziem do tego celu jest funkcja INDEX połączona z dwoma wywołaniami MATCH, często zapisywana jako INDEX-MATCH-MATCH.

Funkcja INDEX zwraca wartość z zakresu na podstawie jej położenia. Pełna postać to INDEX(array, row_num, column_num).

Należy podać blok komórek, numer wiersza i numer kolumny, a funkcja zwróci wartość znajdującą się w tym miejscu. Na przykład w siatce rozpoczynającej się od B2 żądanie wiersza 3 i kolumny 2 zwróci wartość znajdującą się 3 wiersze w dół i 2 kolumny w prawo wewnątrz tego bloku.

Najważniejsza zasada: funkcja INDEX potrzebuje pozycji, a nie etykiet. Właśnie takie pozycje dostarcza funkcja MATCH.

Funkcja MATCH znajduje pozycję wartości w pojedynczym wierszu lub kolumnie. Jej postać to MATCH(lookup_value, lookup_array, match_type).

Jako typ dopasowania należy użyć wartości 0, aby uzyskać dokładne dopasowanie. Wynikiem jest liczba określająca pozycję wartości, licząc od 1.

Jeśli wartość „East” jest drugim elementem zakresu A2:A5, funkcja MATCH zwróci 2. Ta wartość 2 może posłużyć jako numer wiersza dla funkcji INDEX.

W przypadku wyszukiwania dwukierunkowego funkcję MATCH wywołuje się dwukrotnie:

• Jedna funkcja MATCH znajduje wiersz, w którym znajduje się dany region.
• Druga funkcja MATCH znajduje kolumnę, w której znajduje się dany miesiąc.

Następnie obie liczby przekazuje się do funkcji INDEX. Funkcja MATCH wyszukująca wiersz przeszukuje pionowy zakres etykiet, a funkcja MATCH wyszukująca kolumnę — poziomy zakres nagłówków.

Wynikiem jest pojedyncza komórka znajdująca się na przecięciu danego wiersza i kolumny.

Wyobraźmy sobie następujący układ. Etykiety regionów znajdują się w zakresie A2:A5 (East, West, North, South). Nagłówki miesięcy znajdują się w zakresie B1:D1 (Jan, Feb, Mar). Rzeczywiste wartości sprzedaży wypełniają zakres B2:D5.

Wyszukiwanie opiera się na dwóch komórkach wejściowych: G1 zawiera szukany region, a G2 — szukany miesiąc.

Celem jest utworzenie jednej formuły, która odczyta wartości z G1 i G2 oraz zwróci pasującą wartość sprzedaży z zakresu B2:D5.

Najpierw należy znaleźć region. Funkcja MATCH przeszukuje pionową listę etykiet A2:A5 w poszukiwaniu wartości wpisanej w G1.

Jeśli G1 zawiera wartość „North”, a North jest trzecią etykietą, funkcja MATCH zwróci 3.

Ta liczba wskazuje funkcji INDEX, który wiersz bloku danych należy odczytać. Należy zauważyć, że przeszukujemy A2:A5, czyli wyłącznie etykiety, a nie dane. Dzięki temu pozycja 3 odpowiada trzeciemu wierszowi danych.

Następnie należy znaleźć miesiąc. Ta funkcja MATCH przeszukuje poziomy wiersz nagłówków B1:D1 w poszukiwaniu wartości z G2.

Jeśli G2 zawiera wartość „Feb”, a Feb jest drugim nagłówkiem, funkcja MATCH zwróci 2.

Ta liczba stanie się pozycją kolumny dla funkcji INDEX. Podobnie jak w przypadku wierszy, przeszukujemy tylko nagłówki B1:D1, aby pozycja odpowiadała kolumnom danych w zakresie B2:D5.

Teraz należy umieścić oba wywołania MATCH wewnątrz funkcji INDEX. Blok danych B2:D5 jest tablicą, funkcja MATCH dla wiersza dostarcza numer wiersza, a funkcja MATCH dla kolumny — numer kolumny.

Gdy G1 zawiera wartość „North”, a G2 — „Feb”, funkcja MATCH dla wiersza zwraca 3, a funkcja MATCH dla kolumny zwraca 2. W rezultacie INDEX zwraca wartość z wiersza 3 i kolumny 2 zakresu B2:D5.

Ta pojedyncza formuła stanowi kompletne wyszukiwanie dwukierunkowe.

Załóżmy, że zakres B2:D5 zawiera następujące dane: wiersz North to Jan 50, Feb 80, Mar 65.

• MATCH("North", A2:A5, 0) zwraca 3.
• MATCH("Feb", B1:D1, 0) zwraca 2.
• INDEX(B2:D5, 3, 2) odczytuje wiersz 3 i kolumnę 2, zwracając 80.

Zmień G1 na "East" lub G2 na "Mar", a cała formuła zostanie natychmiast przeliczona. Na tym polega siła sterowania funkcją INDEX za pomocą dwóch wyszukiwań MATCH.

VLOOKUP przeszukuje tylko pierwszą kolumnę i zwraca wartość znajdującą się w stałej liczbie kolumn na prawo od niej. Aby zmieniać miesiące, trzeba byłoby samodzielnie wpisać lub obliczyć indeks kolumny.

INDEX-MATCH-MATCH pozwala dynamicznie wybierać zarówno wiersz, jak i kolumnę na podstawie etykiety. Można zmieniać kolejność kolumn i dodawać nowe miesiące, a formuła nadal działa, ponieważ dopasowuje tekst nagłówka, a nie stałą liczbę kolumn.

Najczęstszym błędem są niezgodne zakresy. Zakres MATCH wyszukujący wiersz musi mieć taką samą wysokość jak blok danych INDEX, a zakres MATCH wyszukujący kolumnę musi mieć taką samą szerokość.

Zakres A2:A5 ma 4 wiersze, podobnie jak B2:D5, więc wynik MATCH równy 3 rzeczywiście oznacza trzeci wiersz danych. Jeśli przypadkowo przeszukasz A1:A5, który zawiera nagłówek, pozycje przesuną się o jeden i otrzymasz niewłaściwą komórkę.

Sprawdź, czy rozumiesz wzorzec wyszukiwania dwukierunkowego.

Poznałeś(-aś) wzorzec wyszukiwania dwukierunkowego:

• INDEX zwraca wartość znajdującą się na wskazanej pozycji wiersza i kolumny w obrębie bloku.
• Jedna funkcja MATCH znajduje wiersz, przeszukując etykiety ułożone pionowo.
• Druga funkcja MATCH znajduje kolumnę, przeszukując nagłówki ułożone poziomo.

Połączona formuła =INDEX(data, MATCH(row), MATCH(col)) odczytuje dwa dane wejściowe i zwraca wartość z miejsca ich przecięcia. Aby uniknąć niezgodności, zakresy MATCH powinny mieć taki sam rozmiar jak blok danych.