WYSZUKAJ.POZIOMO w Excelu: dokładne wyniki i progi

Masz produkty w pierwszym wierszu, a pod nimi ceny i stany. Ja najpierw sprawdzam kierunek danych: tutaj szukam w poziomie, więc do starszego skoroszytu pasuje WYSZUKAJ.POZIOMO, po angielsku HLOOKUP. Funkcja znajduje kolumnę na podstawie pierwszego wiersza zakresu i zwraca wartość z wybranego wiersza tej samej kolumny.

Najważniejszy jest czwarty argument. Do szukania konkretnego produktu wpisuję FAŁSZ. Pominięcie argumentu oznacza tryb przybliżony, który wymaga rosnącej kolejności w pierwszym wierszu. Te zasady potwierdza dokumentacja Microsoft. Małe przykłady poniżej są syntetyczne.

Składnia i numer wiersza

=WYSZUKAJ.POZIOMO(szukana_wartość;tabela_tablica;nr_indeksu_wiersza;[przeszukiwany_zakres])

Trzeci argument liczy wiersze wewnątrz podanego zakresu, zaczynając od 1. Nie podajesz numeru wiersza arkusza. Gdy zakres zaczyna się w A10, jego drugi wiersz znajduje się w arkuszowym wierszu 11. W przykładach używam polskich nazw i średników; separator argumentów zależy od ustawień regionalnych. Funkcja jest dostępna między innymi w Excelu 2016, 2019, 2021, 2024 i Microsoft 365.

Zanim napiszę formułę, wskazuję palcem trzy rzeczy: pierwszy wiersz zakresu, szukaną kolumnę i wiersz z wynikiem. Jeśli nie potrafię tego zrobić na małej tabeli, większy zakres tylko ukryje pomyłkę. Szczególnie łatwo policzyć numer od początku arkusza zamiast od początku zaznaczenia.

Cena i stan z jednej tabeli

Wpisz poniższą tabelę w A1:D3. Kolumna A zawiera opisy, a produkty zajmują B:D. Kwoty wpisz jako liczby, stan jako liczbę sztuk. Dane w obu wersjach językowych są takie same.

=WYSZUKAJ.POZIOMO("Banany";$A$1:$D$3;2;FAŁSZ)

Szukanie prowadzi do kolumny C. Drugi wiersz zakresu to wiersz ceny, więc wynikiem jest 4,20. Dla trzeciego argumentu równego 3 otrzymasz 0, ponieważ w C3 zapisano zero sztuk. Znaki dolara utrzymują granice zakresu podczas kopiowania formuły. Wpisana na stałe nazwa może zostać zastąpiona odwołaniem do F1, gdzie użytkownik wybierze produkt.

Dla F1 równego „Pomarańcze” formuła =WYSZUKAJ.POZIOMO(F1;$A$1:$D$3;2;FAŁSZ) wskazuje D2, czyli 5,00. Zmienia się kolumna, ale numer wiersza wyniku pozostaje ten sam. To wygodny sposób na zrozumienie, które argumenty określają miejsce szukania, a które wybierają rodzaj pobieranej informacji.

Brak produktu i błędny indeks

Nazwa „Gruszki” nie występuje w pierwszym wierszu, więc dokładne wyszukiwanie daje #N/D (#N/A w EN). To inna sytuacja niż zero stanu dla bananów. Nie zastępuję obu przypadków zerem, bo odbiorca straciłby możliwość odróżnienia produktu bez zapasu od produktu nieobecnego w słowniku. Microsoft opisuje diagnostykę braku dopasowania.

W tym trzywierszowym zakresie indeks 4 wychodzi poza tabelę i daje #ADR! (#REF!). Indeks mniejszy od 1 daje #ARG! (#VALUE!). Jeśli oczekujesz ceny, a widzisz stan, sprawdź również pozornie poprawny indeks 3: formuła działa, ale odpowiada na inne pytanie. Sam brak błędu nie jest kontrolą merytoryczną.

Przed wyszukiwaniem popraw zbędne spacje i ustal jednolity zapis kluczy. Jeśli nagłówek produktu pojawia się kilka razy, doprecyzuj klucz, na przykład o wariant lub magazyn. W tym tekście zakładam unikalne nagłówki i nie używam wyboru pojedynczego trafienia jako sposobu rozstrzygania duplikatów. Najpierw potrzebna jest reguła biznesowa.

Dopasowanie przybliżone służy progom

W oddzielnym miejscu, B6:E7, wpisz uporządkowane progi. Same progi są liczbami, a drugi wiersz zawiera rzeczywiste wartości procentowe: 0%, 5%, 10%, 15%.

=WYSZUKAJ.POZIOMO(70;$B$6:$E$7;2;PRAWDA)

Największy próg nieprzekraczający 70 to 50, zatem wynik wynosi 10%. Dla 50 również jest to 10%, dla 49 jest 5%, a dla 0 jest 0%. Ujemna liczba znajduje się poniżej pierwszego progu i daje brak dopasowania. W rzeczywistym formularzu zamówienia warto odrzucić takie wejście przed wyliczeniem rabatu.

Sortujemy pierwszy wiersz rosnąco od lewej do prawej, zachowując przypisanie rabatów do progów. Tryb przybliżony nie oznacza matematycznie najbliższej liczby po dowolnej stronie. Dla 90 nadal wybierze próg 50, choć odległość do 100 jest mniejsza. Ta różnica ma znaczenie: czytelna tabela reguł pomaga ją zauważyć wcześniej niż sam wynik w formularzu.

X.WYSZUKAJ dla nowszego skoroszytu

Dla tabeli produktów z początku tekstu właściwy poziomy odpowiednik wygląda tak:

=X.WYSZUKAJ("Banany";$B$1:$D$1;$B$2:$D$2)

Szukanie odbywa się w wierszu produktów B1:D1, a zwracanie w wierszu cen B2:D2. Wynik to 4,20. Zakresy A1:A2 i B1:B2 byłyby pionowe i nie odpowiadałyby temu układowi. Porównuję więc nie tylko długości zakresów, ale także ich kierunek i znaczenie.

X.WYSZUKAJ domyślnie szuka dokładnie. Microsoft wskazuje dostępność w Microsoft 365 oraz Excelu 2021 i 2024, a brak w Excelu 2016 i 2019; potwierdza to dokumentacja XLOOKUP. Dla nowej pracy sprawdziłbym tę opcję, pamiętając o wersji u odbiorcy. Więcej przykładów znajdziesz w haśle X.WYSZUKAJ.

Ćwiczenie z dwoma rodzajami wyniku

Ustaw w F1 „Jabłka”, pobierz cenę, a następnie stan. Oczekuj 3,50 oraz 12. Zmień produkt na „Gruszki” i opisz różnicę między brakiem dopasowania a stanem zero. W tabeli progów sprawdź ręcznie wartości 9, 10 i 100: odpowiadają im 0%, 5% i 15%. Na koniec zapisz jednym zdaniem, dlaczego produkt wymaga dopasowania dokładnego, a rabat korzysta z uporządkowanych progów.