Zastanówmy się, jak szybko połączyć dwie duże tabele wraz z kolumnami i wierszami, czyli dokonać selekcji nie jednym parametrem (jak funkcje VLOOKUP lub HLOOKUP), ale dwoma naraz (przy użyciu funkcji INDEX i MATCH).
Jeśli znasz funkcję VLOOKUP lub jej poziomy odpowiednik HLOOKUP, to powinieneś pamiętać, że te wspaniałe funkcje wyszukują informacje tylko według jednego parametru, czyli w tablicy jednowymiarowej - według wiersza lub kolumny. A jeśli mamy wybrać dane z dwuwymiarowej tabeli przez zbieżność dwóch parametrów na raz - jednocześnie wiersza i kolumny? Rozważmy kilka opcji łączenia tabel.
Załóżmy, że musimy połączyć dwie tabele przedstawiające portfel pożyczek i portfel zabezpieczeń:
Możemy zacząć używać funkcji VLOOKUP do łączenia poszczególnych kolumn, ale jeśli nasze tabele mają wyjątkowo dużą liczbę wierszy i kolumn, to ćwiczenie może okazać się naprawdę uciążliwe. Istnieje jednak dość proste wyjście z tej sytuacji, ponieważ Excel ma dwie doskonałe funkcje, INDEX i MATCH z kategorii Referencje i tablice
OK, więc co musimy zrobić, aby szybko połączyć oba stoły? Zacznijmy od skopiowania nagłówka drugiej tabeli (tej, do której będziemy dołączać) i wklejenia go obok nagłówka pierwszej tabeli. Pod nazwą czapki, funkcja MATCH poda nam numer seryjny kolumny, a zgodnie z numerem transakcji - numer seryjny potrzebnej nam linii.
W rzeczywistości chcemy znaleźć wartość komórki na przecięciu określonego wiersza i kolumny w tabeli. Dla przejrzystości podzielmy zadanie na trzy etapy:
Łącząc wszystkie powyższe w jedną formułę, otrzymujemy komórkę F14 następującą formułę:
=INDEX(J2:M18; MATCH(A14; K2:K18; 0); MATCH(F1; J1:M1; 0))
W ten sposób, rozciągając naszą formułę na cały zakres, poprawnie dołączymy dodatkowe pola. Należy również zwrócić uwagę, że rozciągając formułę, musimy ustalić zakresy znakiem dolara ($) (w celu ustalenia można użyć klawisza F4), w celu wyszukiwania po kapitale popraw tylko linię ( 1 F$ ), do wyszukiwania po numerach transakcji - tylko kolumna ( $A14 ).
Zmodyfikujmy nieco poprzedni przykład i załóżmy, że mamy podobne tabele, ale ich nagłówki są połączone, więc funkcja MATCH nie pomoże nam poprawnie określić numeru seryjnego kolumny.
W takim przypadku możemy utworzyć nad tabelą pole techniczne i ręcznie wstawić numery kolumn. Następnie możemy skorzystać ze znanej już funkcji VLOOKUP.
Zatem wzór dla naszej drugiej opcji będzie następujący:
=VLOOKUP($A3; $J$3:$M$19; E$1; 0)
Ponownie nie zapomnij o zaznaczeniu zakresu i odpowiadających mu pól wyszukiwania znakiem $ , aby formuła działała poprawnie i nie dawała błędu przy przesuwaniu zakresów.