Wyszukiwanie wierszy za pomocą HLOOKUP

HLOOKUP to poziomy odpowiednik funkcji VLOOKUP. Litera H oznacza Horizontal (poziomo): zamiast przeszukiwać kolumnę, funkcja przeszukuje górny wiersz tabeli, a następnie zwraca wartość z dalszej części tej samej kolumny.

Funkcji HLOOKUP należy używać, gdy dane są ułożone poziomo, a etykiety znajdują się w wierszu zamiast w kolumnie.

Funkcja HLOOKUP działa podobnie do VLOOKUP, z jedną kluczową zmianą. Jej argumenty to:

• lookup_value — wartość wyszukiwana w górnym wierszu
• table_array — zakres komórek
• row_index_num — numer wiersza, z którego należy zwrócić wartość (liczony od góry)
• [range_lookup] — TRUE oznacza dopasowanie przybliżone, a FALSE dokładne

Proszę zauważyć, że trzeci argument jest teraz numerem wiersza, a nie kolumny, ponieważ dane są ułożone pionowo w dół.

Wyobraźmy sobie kwartalną tabelę, w której miesiące biegną w poprzek wiersza 1 w zakresie A1:D3:

• Wiersz 1: Jan, Feb, Mar, Apr
• Wiersz 2 (Sales): 100, 150, 120, 200
• Wiersz 3 (Costs): 60, 70, 65, 90

To górny wiersz (miesiące) jest przeszukiwany przez funkcję HLOOKUP. Wiersze poniżej zawierają dane, które chcemy zwrócić.

Aby znaleźć wartość sprzedaży dla miesiąca Mar, funkcja HLOOKUP przeszukuje górny wiersz w poszukiwaniu wartości „Mar”, a następnie zwraca wartość z wiersza 2 (Sales):

Mówiąc prościej: należy wyszukać "Mar" w górnym wierszu zakresu A1:D3 i zwrócić wartość z 2. wiersza, używając dopasowania dokładnego. Wynik to 120.

Wartość row_index_num jest liczona od góry zakresu table_array, a nie od wiersza 1 arkusza.

W naszym zakresie A1:D3 wiersze mają następujące numery:

• Wiersz 1 = miesiące (wiersz wyszukiwania)
• Wiersz 2 = Sales
• Wiersz 3 = Costs

Aby zwrócić wartość Costs dla danego miesiąca, należy użyć numeru wiersza 3. Indeks 1 zwraca po prostu wyszukaną etykietę.

Podobnie jak w przypadku funkcji VLOOKUP, zwykle wskazuje się komórkę zamiast wpisywać etykietę. Jeśli miesiąc zostanie wybrany w komórce F1, należy odwołać się do niej bezpośrednio.

Zmiana wartości F1 na inny miesiąc natychmiast aktualizuje wynik. Dzięki temu HLOOKUP świetnie sprawdza się w przypadku niewielkiego selektora odczytującego dane z poziomej tabeli podsumowującej.

Obie funkcje działają według tej samej zasady, ale są obrócone o 90 stopni:

• VLOOKUP: przeszukuje pierwszą kolumnę w dół, a trzeci argument określa numer kolumny
• HLOOKUP: przeszukuje górny wiersz w poprzek, a trzeci argument określa numer wiersza

Wyboru należy dokonać na podstawie układu danych. Jeśli nagłówki biegną wzdłuż boku, należy użyć VLOOKUP. Jeśli biegną w poprzek u góry, należy użyć HLOOKUP.

Argument range_lookup działa identycznie jak w funkcji VLOOKUP:

• FALSE = dopasowanie dokładne; górny wiersz nie musi być posortowany, a brakujące wartości powodują błąd #N/A
• TRUE = dopasowanie przybliżone; górny wiersz musi być posortowany rosnąco od lewej do prawej

W przypadku wyszukiwania etykiet, takich jak nazwy miesięcy, należy używać FALSE. Wartość TRUE działa w przypadku poziomego wiersza progów, ale rzadko układa się przedziały poziomo.

Jeśli kopiują Państwo funkcję HLOOKUP w poprzek kilku komórek, należy zablokować table_array za pomocą znaków dolara, aby się nie przesuwał. lookup_value należy pozostawić jako odwołanie względne, jeśli powinno zmieniać się wraz z każdą kolumną.

W tym przypadku kopiowanie w prawo pozostawia tabelę w tym samym miejscu, a etykieta wyszukiwania przesuwa się do następnej komórki. Obowiązuje tu ta sama zasada co w przypadku funkcji VLOOKUP, tylko w kierunku poziomym.

Tabele z układem poziomym pojawiają się częściej, niż można by się spodziewać:

• Modele finansowe, w których lata lub miesiące są kolumnami
• Arkusze wyników z rundami ułożonymi w górnym wierszu
• Importowane raporty, w których okresy znajdują się w wierszu nagłówka

Zamiast transponować całą tabelę, HLOOKUP pozwala przeszukiwać ją bez zmian. Warto jednak pamiętać, że wiele osób woli przechowywać dane pionowo, ponieważ większość funkcji lepiej obsługuje taki układ, a wtedy VLOOKUP lub XLOOKUP pasuje naturalnie.

Podobnie jak w przypadku VLOOKUP, wpisanie indeksu wiersza na stałe może być podatne na błędy. Jeśli wstawisz wiersz, indeks przestanie być poprawny. Możesz uczynić go dynamicznym, wyszukując pozycję wiersza za pomocą funkcji MATCH.

W tym przypadku MATCH ustala, w którym wierszu, wśród etykiet w kolumnie A, znajduje się etykieta "Costs", a następnie przekazuje tę liczbę do HLOOKUP. Dzięki temu formuła nadal działa, nawet jeśli wiersze zmienią kolejność. To pierwszy przykład łączenia funkcji wyszukiwania — do tego tematu wrócisz później.

Sprawdź, jak dobrze rozumiesz wyszukiwanie poziome.

Potrafisz już przeszukiwać tabele z układem poziomym:

• HLOOKUP przeszukuje górny wiersz i zwraca wartość z wiersza poniżej
• Trzeci argument to row_index_num, liczony od góry zakresu
• FALSE i TRUE działają tak samo jak w VLOOKUP w przypadku dopasowania dokładnego i przybliżonego
• Podczas kopiowania w poprzek zablokuj tabelę za pomocą $
• Używaj tej funkcji, gdy nagłówki są ułożone poziomo, a nie pionowo

W następnej części dowiesz się, dlaczego VLOOKUP czasami zawodzi i jak to diagnozować.