Jak działa funkcja X.WYSZUKAJ w Excelu?

Czym dokładnie jest X.WYSZUKAJ i do czego służy?

X.WYSZUKAJ to funkcja wyszukiwania danych wprowadzona w Excelu 365, która zastępuje WYSZUKAJ.PIONOWO, WYSZUKAJ.POZIOMO oraz kombinację INDEKS i PODAJ.POZYCJĘ. Przyjmuje szukaną wartość, przeszukuje wskazany zakres komórek i zwraca wynik z innego zakresu w tym samym wierszu lub kolumnie. Domyślnie stosuje dopasowanie dokładne, co eliminuje częsty błąd WYSZUKAJ.PIONOWO zwracającego nieprawidłowy wynik przy nieposortowanych danych. W angielskiej wersji Excela nosi nazwę XLOOKUP. Funkcja nie wymaga numerowania kolumn, działa w lewo i w prawo od przeszukiwanego zakresu, a brak wyniku obsługuje wbudowanym argumentem zamiast osobnej formuły JEŻELI.BŁĄD. Microsoft udostępnił ją w ramach subskrypcji Microsoft 365 oraz w Excelu 2021 i nowszych. Starsze wersje programu (Excel 2019, 2016) nie rozpoznają tej funkcji i wyświetlą błąd przy otwarciu pliku, który ją zawiera.

Jak wygląda składnia X.WYSZUKAJ i co oznacza każdy argument?

Składnia funkcji X.WYSZUKAJ zawiera sześć argumentów, z których trzy są wymagane do działania formuły, a trzy pozostałe pozwalają kontrolować zachowanie przy braku wyniku, typ dopasowania i kierunek przeszukiwania. Pełna formuła wygląda tak: X.WYSZUKAJ(szukana_wartość; szukana_tablica; zwracana_tablica; jeżeli_nie_znaleziono; tryb_dopasowania; tryb_wyszukiwania). W praktyce większość formuł korzysta tylko z trzech pierwszych argumentów, a opcjonalne dodaję wtedy, gdy potrzebuję obsłużyć błędy lub zmienić logikę przeszukiwania.

Trzy obowiązkowe argumenty – szukana wartość, tablica i zwracana tablica

Pierwszy argument, szukana_wartość, to dane które funkcja ma znaleźć w przeszukiwanym zakresie. Może to być tekst, liczba, data lub odwołanie do komórki. Drugi argument, szukana_tablica, wskazuje zakres komórek (jedną kolumnę lub jeden wiersz), w którym Excel szuka dopasowania. Trzeci argument, zwracana_tablica, określa zakres z którego funkcja pobiera wynik. Ten zakres nie musi sąsiadować z przeszukiwanym ani znajdować się po jego prawej stronie. Rozdzielenie tablicy wyszukiwania od tablicy wynikowej na dwa osobne argumenty sprawia, że dodanie nowej kolumny do arkusza nie psuje formuły.

Trzy opcjonalne argumenty – obsługa błędów, tryb dopasowania i kierunek

Czwarty argument, jeżeli_nie_znaleziono, przyjmuje dowolną wartość zwracaną gdy X.WYSZUKAJ nie znajdzie dopasowania. Bez niego funkcja zwróci błąd #N/D. Piąty argument, tryb_dopasowania, przyjmuje jedną z czterech wartości: 0 dla dokładnego dopasowania (ustawienie domyślne), -1 dla najbliższej mniejszej wartości, 1 dla najbliższej większej wartości oraz 2 dla dopasowania z symbolami wieloznacznymi (* i ?). Szósty argument, tryb_wyszukiwania, steruje kierunkiem przeszukiwania: 1 oznacza przeszukiwanie od pierwszego do ostatniego elementu, -1 od ostatniego do pierwszego, a wartości 2 i -2 uruchamiają szybsze wyszukiwanie binarne na posortowanych danych.

Jak zastąpić WYSZUKAJ.PIONOWO funkcją X.WYSZUKAJ?

Zamiana istniejącej formuły WYSZUKAJ.PIONOWO na X.WYSZUKAJ sprowadza się do rozbicia jednego argumentu tablicowego na dwa osobne zakresy. Zamiast wskazywać całą tabelę i numer kolumny wynikowej, podajesz oddzielnie kolumnę przeszukiwaną i kolumnę ze zwracanymi danymi. Poniżej porównanie obu formuł dla tego samego zadania, gdzie szukam nazwy działu na podstawie numeru ID pracownika:

1. Stara formuła: =WYSZUKAJ.PIONOWO(A2; A:C; 3; FAŁSZ) wymaga podania numeru kolumny (3) i ogranicza przeszukiwanie do pierwszej kolumny zakresu A:C.
2. Nowa formuła: =X.WYSZUKAJ(A2; A:A; C:C) wskazuje kolumnę przeszukiwaną (A:A) i kolumnę wynikową (C:C) jako osobne argumenty.
3. Argument FAŁSZ z WYSZUKAJ.PIONOWO staje się zbędny, bo X.WYSZUKAJ domyślnie stosuje dopasowanie dokładne.
4. Jeśli oryginalna formuła używała JEŻELI.BŁĄD do obsługi braku wyniku, w X.WYSZUKAJ zastąp ją czwartym argumentem: =X.WYSZUKAJ(A2; A:A; C:C; „brak danych”).
5. Przy formulach wyszukujących w lewo od kolumny klucza, gdzie WYSZUKAJ.PIONOWO wymagał kombinacji z INDEKS i PODAJ.POZYCJĘ, X.WYSZUKAJ działa bezpośrednio, bo zwracana_tablica może znajdować się w dowolnym miejscu arkusza.

Jak X.WYSZUKAJ obsługuje błąd #N/D bez funkcji JEŻELI.BŁĄD?

X.WYSZUKAJ eliminuje błąd #N/D dzięki wbudowanemu czwartemu argumentowi jeżeli_nie_znaleziono. Wpisujesz w nim tekst, liczbę, pustą wartość lub odwołanie do komórki, a funkcja zwróci tę wartość zamiast błędu gdy nie znajdzie dopasowania. Formuła =X.WYSZUKAJ(A2; B:B; C:C; „brak wyniku”) przy braku dopasowania wyświetli tekst „brak wyniku” zamiast #N/D. W starszym podejściu ten sam efekt wymagał opakowywania WYSZUKAJ.PIONOWO w funkcję JEŻELI.BŁĄD lub CZY.BŁĄD, co wydłużało formułę i pogarszało czytelność. Zostawienie czwartego argumentu pustym przywraca domyślne zachowanie z błędem #N/D. Stosuję ten argument w każdym pliku współdzielonym z innymi osobami, bo czytelny komunikat tekstowy jest zawsze lepszy niż surowy kod błędu w komórce raportu.

Przy budowaniu dashboardów wpisuję w czwarty argument liczbę 0 zamiast tekstu. Dzięki temu wykresy i tabele przestawne podłączone do komórek z X.WYSZUKAJ nie przerywają obliczeń na błędzie, a brakujące dane po prostu nie zawyżają sum ani średnich.

Jak wyszukać ostatnią wartość na liście za pomocą X.WYSZUKAJ?

Szósty argument funkcji X.WYSZUKAJ ustawiony na -1 przeszukuje zakres od ostatniego elementu do pierwszego i zwraca ostatnie dopasowanie. WYSZUKAJ.PIONOWO zawsze zwracał pierwszy znaleziony wynik, a dotarcie do ostatniego wymagało formuł tablicowych z INDEKS, PODAJ.POZYCJĘ i WYSZUKAJ. X.WYSZUKAJ realizuje to jednym argumentem. Sprawdza się wszędzie tam, gdzie ta sama wartość pojawia się w kolumnie wielokrotnie, a potrzebujesz najnowszego wpisu, np. ostatniej ceny produktu, ostatniego statusu zamówienia lub najświeższej daty kontaktu z klientem.

1. Zapisz formułę z trzema obowiązkowymi argumentami: =X.WYSZUKAJ(E2; A:A; C:C).
2. Dodaj czwarty argument na wypadek braku wyniku: =X.WYSZUKAJ(E2; A:A; C:C; „brak”).
3. Pomiń piąty argument (tryb dopasowania) lub wpisz 0 dla dopasowania dokładnego.
4. W szóstym argumencie wpisz -1, co przełącza kierunek przeszukiwania od dołu listy: =X.WYSZUKAJ(E2; A:A; C:C; „brak”; 0; -1).
5. Funkcja przejdzie przez zakres od ostatniego wiersza w górę i zatrzyma się na pierwszym trafieniu, czyli de facto ostatnim wystąpieniu szukanej wartości w danych.

Czy wiesz, że zmiana szóstego argumentu na wartość 2 lub -2 aktywuje wyszukiwanie binarne, które na posortowanych danych działa szybciej niż przeszukiwanie liniowe? Przy tabelach z kilkudziesięcioma tysiącami wierszy różnica w czasie przeliczania formuł jest odczuwalna.

Jak działa wyszukiwanie przybliżone w X.WYSZUKAJ?

Wyszukiwanie przybliżone w X.WYSZUKAJ zwraca najbliższą wartość gdy dokładne dopasowanie nie istnieje w przeszukiwanym zakresie. Kontroluje je piąty argument tryb_dopasowania. Domyślne ustawienie 0 wymusza dopasowanie dokładne, a dane nie muszą być posortowane rosnąco, żeby przybliżenie zadziałało poprawnie. Stosuję ten tryb przy wyszukiwaniu progów podatkowych, przedziałów rabatowych i stawek prowizji, gdzie szukana kwota prawie nigdy nie trafia dokładnie w wartość z tabeli.

• Tryb -1 (najbliższa mniejsza wartość) znajduje największy element w zakresie nieprzekraczający szukanej. Dla progów 0, 5000, 10000 i szukanej wartości 7500 zwróci wynik przypisany do 5000. Odpowiada logice tablic podatkowych i cenników, gdzie stawka obowiązuje od progu w górę.
• Tryb 1 (najbliższa większa wartość) działa odwrotnie i zwraca najmniejszy element równy lub większy od szukanej. Dla tych samych progów i wartości 7500 zwróci wynik powiązany z 10000. Sprawdza się przy wyszukiwaniu najbliższego terminu dostawy lub następnego progu darmowej wysyłki.

Jak połączyć X.WYSZUKAJ z funkcjami FILTRUJ, SORTUJ i JEŻELI?

X.WYSZUKAJ w połączeniu z innymi funkcjami dynamicznymi Excela 365 tworzy formuły, które automatycznie reagują na zmiany danych bez ręcznej modyfikacji. Zagnieżdżam ją najczęściej z trzema funkcjami: FILTRUJ, SORTUJ i JEŻELI. Każde połączenie rozwiązuje inny problem analityczny.

• X.WYSZUKAJ + JEŻELI: formuła =JEŻELI(X.WYSZUKAJ(A2; B:B; C:C; „”)=””; „nie znaleziono”; „dostępny”) sprawdza czy produkt istnieje w bazie i zwraca czytelny komunikat. X.WYSZUKAJ z pustym czwartym argumentem zwraca pusty tekst zamiast #N/D, a JEŻELI interpretuje wynik.
• X.WYSZUKAJ + FILTRUJ: formuła =FILTRUJ(B:B; A:A=X.WYSZUKAJ(E1; C:C; A:A)) najpierw wyszukuje wartość klucza przez X.WYSZUKAJ, a potem FILTRUJ zwraca wszystkie powiązane wiersze. Przydaje się gdy jedno wyszukanie musi zwrócić wiele wyników zamiast jednego.
• X.WYSZUKAJ + SORTUJ: formuła =SORTUJ(X.WYSZUKAJ(E1; A:A; B:F)) pobiera cały wiersz danych przez X.WYSZUKAJ ze zwracaną tablicą obejmującą wiele kolumn, a SORTUJ porządkuje wynik. Stosuję to przy generowaniu fragmentów raportów, gdzie kolejność kolumn w źródle nie odpowiada kolejności w raporcie.
• Zagnieżdżony X.WYSZUKAJ: dwie funkcje X.WYSZUKAJ osadzone jedna w drugiej realizują wyszukiwanie na przecięciu wiersza i kolumny. Zewnętrzna funkcja wyszukuje w pionie, wewnętrzna w poziomie po nagłówkach kolumn. Zastępuje to dawną kombinację INDEKS z dwoma funkcjami PODAJ.POZYCJĘ.

Które wersje Excela obsługują X.WYSZUKAJ?

X.WYSZUKAJ działa w Microsoft 365 (dawniej Office 365), Excelu 2021, Excelu 2024 oraz w wersji przeglądarkowej Excel dla sieci Web. Wersje Excel 2019, 2016 i starsze nie obsługują tej funkcji. Otwarcie pliku z formułą X.WYSZUKAJ w nieobsługiwanej wersji wyświetli błąd #NAZWA? w każdej komórce zawierającej tę funkcję. Przy współdzieleniu plików z osobami korzystającymi ze starszych wersji jedyną alternatywą pozostaje kombinacja INDEKS i PODAJ.POZYCJĘ, która daje podobną elastyczność wyszukiwania i jest kompatybilna wstecz aż do Excela 2007. Excel na iPada, iPhona i urządzenia z Androidem obsługuje X.WYSZUKAJ pod warunkiem aktywnej subskrypcji Microsoft 365.

Kiedy lepiej użyć INDEKS i PODAJ.POZYCJĘ zamiast X.WYSZUKAJ?

INDEKS z PODAJ.POZYCJĘ pozostaje lepszym wyborem w trzech sytuacjach. Pierwsza to kompatybilność wsteczna, gdy plik trafia do osób pracujących na Excelu 2019 lub starszym. Druga to bardzo duże zbiory danych przekraczające kilkaset tysięcy wierszy, gdzie PODAJ.POZYCJĘ z posortowanym zakresem i wyszukiwaniem binarnym bywa szybsze niż liniowe przeszukiwanie X.WYSZUKAJ. Trzecia to formuły wymagające dynamicznego numeru wiersza lub kolumny jako wyniku pośredniego, np. przy budowaniu złożonych odwołań w makrach VBA lub w formułach warunkowego formatowania. INDEKS zwraca odwołanie do komórki, a X.WYSZUKAJ zwraca wartość. Ta różnica ma znaczenie gdy wynik formuły jest argumentem wejściowym dla kolejnej funkcji oczekującej adresu komórki. W pozostałych przypadkach X.WYSZUKAJ daje krótszą, czytelniejszą formułę i nie wymaga ręcznego pilnowania zakresów przy zmianach struktury arkusza.