Funkcja X.WYSZUKAJ w Excelu 365 - następca WYSZUKAJ.PIONOWO

Szukasz ceny po kodzie produktu, daty zamówienia po nazwisku klienta albo ostatniej transakcji danej osoby. Przez lata robiło się to funkcją WYSZUKAJ.PIONOWO (VLOOKUP), która ma kilka uciążliwych ograniczeń. Funkcja X.WYSZUKAJ (XLOOKUP) w Excelu zdejmuje je wszystkie i załatwia każde z tych wyszukiwań jedną formułą.

Najpierw budowa funkcji i jej sześć argumentów. Potem dziesięć przykładów na jednej tabeli z zamówieniami: wyszukiwanie w pionie, w poziomie i w lewo, własny komunikat zamiast błędu, najbliższa data, szukanie po fragmencie nazwiska, ostatnie wystąpienie, cały wiersz naraz, przecięcie wiersza i kolumny oraz wyszukiwanie po dwóch warunkach.

Najciekawszy jest ostatni przykład: dwa warunki w jednej formule, bez kolumny pomocniczej, którą przy WYSZUKAJ.PIONOWO trzeba było doklejać do tabeli. Każdą formułę skopiujesz prosto ze strony, a plik z przykładami pobierzesz na końcu.

W tym poradniku:

1. Jak działa funkcja X.WYSZUKAJ
2. Wyszukiwanie w pionie
3. Wyszukiwanie w poziomie
4. Wyszukiwanie w lewo
5. Własny komunikat zamiast błędu #N/D
6. Najbliższa mniejsza lub większa wartość
7. Szukanie po fragmencie tekstu
8. Ostatnie wystąpienie wartości
9. Cały wiersz jedną formułą
10. Wyszukiwanie na przecięciu wiersza i kolumny
11. X.WYSZUKAJ z dwoma warunkami
12. Podsumowanie i plik do pobrania

Jak działa funkcja X.WYSZUKAJ

X.WYSZUKAJ przeszukuje jeden zakres i zwraca wartość z tego samego wiersza (albo kolumny) innego zakresu. Zastępuje WYSZUKAJ.PIONOWO, WYSZUKAJ.POZIOMO (HLOOKUP) i parę INDEKS z PODAJ.POZYCJĘ.

Funkcja jest w Excelu dla Microsoft 365, w Excelu 2021 i 2024 oraz w Excelu w przeglądarce. W Excelu 2019 i starszych jej nie ma i formuła zwróci tam błąd #NAZWA?.

W angielskim Excelu i w większości poradników z internetu ta sama funkcja nazywa się XLOOKUP. Nazwę Excel przetłumaczy sam przy otwarciu pliku, ale formuły przepisanej z angielskiej strony nie wkleisz wprost: tam argumenty rozdziela przecinek, a w polskim Excelu średnik.

Budowa funkcji:

=X.WYSZUKAJ(szukana_wartość; szukana_tablica; zwracana_tablica; [jeżeli_nie_znaleziono]; [tryb_dopasowywania]; [tryb_wyszukiwania])

Wymagane są trzy pierwsze argumenty. Trzy kolejne, w nawiasach kwadratowych, możesz pominąć:

• szukana_wartośćWartość, której szukasz. Zwykle adres komórki, na przykład z nazwiskiem klienta.
• szukana_tablicaKolumna albo wiersz, w którym Excel szuka tej wartości.
• zwracana_tablicaKolumna albo wiersz, z którego Excel bierze wynik. Musi mieć tyle samo komórek co szukana tablica, inaczej formuła zwróci błąd #ARG!.
• jeżeli_nie_znalezionoOpcjonalny. Co pokazać, gdy wartości nie ma. Pominięty daje błąd #N/D.
• tryb_dopasowywaniaOpcjonalny. Jedna z czterech wartości:
  • 0 dopasowanie dokładne, gdy go nie ma, błąd #N/D. To ustawienie domyślne.
  • -1 dopasowanie dokładne, a gdy go nie ma, najbliższa mniejsza wartość.
  • 1 dopasowanie dokładne, a gdy go nie ma, najbliższa większa wartość.
  • 2 dopasowanie z symbolami wieloznacznymi: gwiazdką, znakiem zapytania i tyldą.
• tryb_wyszukiwaniaOpcjonalny. Kierunek przeszukiwania:
  • 1 od pierwszego elementu. To ustawienie domyślne.
  • -1 od ostatniego elementu.
  • 2 wyszukiwanie binarne w danych posortowanych rosnąco.
  • -2 wyszukiwanie binarne w danych posortowanych malejąco.
  Wyszukiwania binarnego przy zwykłych tabelach nie potrzebujesz. Na nieposortowanych danych daje błędne wyniki i nie ostrzega o tym.

Jedna różnica wobec WYSZUKAJ.PIONOWO, o której warto pamiętać od początku: tam domyślne jest dopasowanie przybliżone i trzeba dopisywać FAŁSZ na końcu formuły. W X.WYSZUKAJ domyślne jest dopasowanie dokładne.

Wszystkie przykłady niżej liczą się na tej samej tabeli: data w kolumnie A, klient w B, wartość zamówienia w C, dane w wierszach od 4 do 19. Szukaną wartość wpisuję w kolumnie E, a formuła stoi obok, w kolumnie F.

Wyszukiwanie w pionie

Najprostszy przypadek, ten sam co w WYSZUKAJ.PIONOWO. Szukam wartości zamówienia Bruno Czajkowskiego:

=X.WYSZUKAJ(E4;B4:B19;C4:C19)

E4 to szukane nazwisko, B4:B19 kolumna z klientami, C4:C19 kolumna z wartościami. Wynik to 113 zł.

Zauważ, że nie podajesz numeru kolumny, jak w WYSZUKAJ.PIONOWO, tylko samą kolumnę z wynikiem. Kiedy ktoś wstawi nową kolumnę w środku tabeli, formuła dalej wskazuje właściwe dane.

Wyszukiwanie w poziomie

Ta sama funkcja przeszukuje też wiersz, czyli zastępuje WYSZUKAJ.POZIOMO. Tu szukam Bruno Czajkowskiego w wierszu 6 i zwracam nagłówek kolumny, w której go znalazła:

=X.WYSZUKAJ(E5;A6:C6;A3:C3)

Wynik to „Klient”.

Wyszukiwanie w lewo

WYSZUKAJ.PIONOWO szuka tylko w pierwszej kolumnie zakresu, więc nie zwróci niczego, co stoi po jej lewej stronie. X.WYSZUKAJ nie ma tego ograniczenia. Szukam daty pierwszego zamówienia Kingi Muchy: klient jest w kolumnie B, a data w kolumnie A, czyli na lewo.

=X.WYSZUKAJ(E6;$B$4:$B$19;$A$4:$A$19)

Wynik to 27.10.2022. Znaki dolara blokują zakresy, żeby po skopiowaniu formuły w dół nie przesuwały się razem z nią.

Własny komunikat zamiast błędu #N/D

Starsze funkcje wyszukiwania przy braku wyniku zwracają #N/D i trzeba je było owijać w JEŻELI.BŁĄD (IFERROR). X.WYSZUKAJ ma na to czwarty argument:

=X.WYSZUKAJ(E7;$B$4:$B$19;$A$4:$A$19;"nie znaleziono")

Gabriela Urbańskiego w tabeli nie ma (jest Gabriela Urbańska), więc formuła pokazuje „nie znaleziono”.

Jest też różnica w działaniu. JEŻELI.BŁĄD ukrywa każdy błąd, także ten z literówki w zakresie. Czwarty argument X.WYSZUKAJ reaguje tylko na brak wyniku, więc inne błędy nadal widać.

Najbliższa mniejsza lub większa wartość

Szukam klienta, który złożył zamówienie 29.10.2022. Tego dnia nie było żadnego, więc w piątym argumencie ustawiam -1: dopasowanie dokładne, a gdy go nie ma, najbliższa mniejsza wartość.

=X.WYSZUKAJ(E8;$A$4:$A$19;$B$4:$B$19;;-1)

Wynik to Artur Krawczyk, który zamawiał 28.10.2022. Dwa średniki pod rząd oznaczają pominięty czwarty argument.

Po zmianie -1 na 1 formuła szuka najbliższej późniejszej daty i zwraca Gabriela Osińskiego (30.10.2022). Dane nie muszą być przy tym posortowane. WYSZUKAJ.PIONOWO w trybie przybliżonym wymaga sortowania rosnąco i bez niego zwraca przypadkowe wyniki.

Szukanie po fragmencie tekstu

Tryb dopasowania 2 pozwala szukać po wzorcu. Gwiazdka zastępuje dowolną liczbę znaków, znak zapytania dokładnie jeden.

=X.WYSZUKAJ(E10;B4:B19;C4:C19;;2)

W komórce E10 jest wzorzec ???????Barański, czyli siedem dowolnych znaków i nazwisko. Pasuje do „Michał Barański” (sześć liter imienia i spacja), więc wynik to 115 zł.

W praktyce prościej wpisać *Barański, wtedy długość imienia nie ma znaczenia. Pamiętaj, że bez trybu 2 gwiazdka i znak zapytania są zwykłymi znakami i formuła zwróci #N/D.

Ostatnie wystąpienie wartości

Domyślnie X.WYSZUKAJ zwraca pierwsze trafienie od góry. Szósty argument ustawiony na -1 odwraca kierunek i funkcja zaczyna od dołu tabeli. Szukam ostatniego zamówienia Zofii Zych:

=X.WYSZUKAJ(E11;B4:B19;C4:C19;;;-1)

Zofia Zych ma w tabeli cztery zamówienia. Formuła zwraca ostatnie z nich, z 05.11.2022 na 112 zł. WYSZUKAJ.PIONOWO tego nie potrafi, zawsze kończy na pierwszym trafieniu.

Cały wiersz jedną formułą

Jeśli zwracana tablica obejmuje kilka kolumn, X.WYSZUKAJ zwraca cały fragment wiersza, a wynik rozlewa się na sąsiednie komórki. Czym jest rozlanie, pokazuję w artykule o dynamicznych funkcjach tablicowych.

=X.WYSZUKAJ(E12;A4:A19;B4:C19)

Dla daty 01.11.2022 formuła wpisana w F12 wypełnia dwie komórki: Artur Krawczyk w F12 i 101 zł w G12. Komórki po prawej muszą być puste, inaczej zamiast wyniku pojawi się błąd #ROZLANIE!.

🚀 Następny poziom

Wyszukiwanie, które zadziała w każdej wersji Excela

✓INDEKS i PODAJ.POZYCJĘ: wyszukiwanie w dowolną stronę

✓WYSZUKAJ.POZIOMO i wyszukiwanie wartości przybliżonych

✓Reguły poprawności danych i naprawa błędów w funkcjach

✓Lekcje wideo: 40 · zadania z rozwiązaniami: 17

Zobacz program kursu

🛡 Gwarancja zwrotu 30 dni · ⚡ Dostęp natychmiastowy

Wyszukiwanie na przecięciu wiersza i kolumny

Tu jedna X.WYSZUKAJ siedzi w drugiej. Wewnętrzna szuka nazwy kolumny (w E15 jest wpisane „Klient”) w nagłówkach B3:C3 i zwraca całą kolumnę o tej nazwie. Zewnętrzna szuka daty 28.10.2022 i z tej kolumny bierze odpowiedni wiersz.

=X.WYSZUKAJ(E14;A4:A19;X.WYSZUKAJ(E15;B3:C3;B4:C19))

Wynik to Artur Krawczyk. Po wpisaniu w E15 „Wartość” ta sama formuła zwróci 68 zł. Dotąd robiło się to parą INDEKS i PODAJ.POZYCJĘ.

X.WYSZUKAJ z dwoma warunkami

Szukam wartości zamówienia Kai Olszewskiej z 04.11.2022. Sama data nie wystarczy, bo tego dnia zamawiały dwie osoby. W praktyce sam klient też zwykle nie wystarcza, bo zamawia więcej niż raz.

=X.WYSZUKAJ(1;(A4:A19=E17)*(B4:B19=E18);C4:C19)

Jak to działa: każde porównanie w nawiasie daje listę wartości PRAWDA i FAŁSZ, po jednej na wiersz tabeli. Mnożenie zamienia je na jedynki i zera, a jedynka zostaje tylko tam, gdzie oba warunki są spełnione naraz. Formuła szuka więc liczby 1 w tej liście i zwraca wartość z tego samego wiersza: 92 zł.

Trzeci warunek dopisujesz kolejnym mnożeniem, na przykład *(C4:C19>50). W starszych wersjach Excela taka konstrukcja wymagała kolumny pomocniczej, która sklejała oba pola w jeden tekst.

Podsumowanie i plik do pobrania

X.WYSZUKAJ zastępuje trzy starsze sposoby wyszukiwania jedną funkcją. O te pięć rzeczy formuła potyka się najczęściej:

• Zakresy różnej długości. Szukana i zwracana tablica muszą mieć tyle samo komórek. A4:A19 z B4:B20 da błąd #ARG!.
• Odwrotne ustawienie domyślne. X.WYSZUKAJ szuka dokładnie, WYSZUKAJ.PIONOWO bez czwartego argumentu szuka w przybliżeniu.
• Symbole wieloznaczne tylko w trybie 2. Bez niego gwiazdka jest zwykłym znakiem.
• Wynik z kilku kolumn potrzebuje miejsca. Zajęta komórka obok daje #ROZLANIE!.
• Wersja Excela. W Excelu 2019 i starszych funkcji nie ma. Plik wysłany komuś z taką wersją przestanie liczyć.

Plik ze wszystkimi dziesięcioma przykładami pobierzesz poniżej.

🚀 Następny poziom

Wyszukiwanie masz opanowane. Pora na resztę funkcji, których używa się w pracy

✓SUMA.WARUNKÓW, LICZ.WARUNKI i własne reguły formatowania warunkowego

✓Tabele przestawne, wykresy przestawne i fragmentatory

✓Pierwsze makra VBA

✓Gwarancja zwrotu: 30 dni

Zobacz kurs

🛡 Gwarancja zwrotu 30 dni · ⚡ Dostęp natychmiastowy