X.WYSZUKAJ w Excelu: dokładne dopasowanie, duplikaty i tryby wyszukiwania

Chcesz dopisać stan magazynowy do listy produktów. Masz identyfikator, a w drugiej tabeli nazwę, dział i liczbę sztuk. Ja zacząłbym od X.WYSZUKAJ, ale przed skopiowaniem formuły sprawdziłbym jedną rzecz: czy identyfikator w tabeli źródłowej występuje tylko raz. Poprawnie działająca funkcja może zwrócić niewłaściwy dla zadania rekord, jeśli ta reguła pozostaje nieustalona.

X.WYSZUKAJ to polska nazwa XLOOKUP. Domyślnie szuka dokładnego dopasowania, od pierwszego elementu. Funkcja jest dostępna w Microsoft 365 oraz Excelu 2021 i 2024; nie ma jej w Excelu 2016 i 2019. Potwierdza to dokumentacja Microsoft, mimo że starsze wersje mogą pojawiać się w ogólnej liście „Dotyczy”.

Formuły poniżej używają polskich nazw i średników. Separator zależy od ustawień regionalnych, więc w innym środowisku może być inny. Dane są syntetyczne, a angielskie etykiety produktów zachowuję także w PL, żeby oba teksty korzystały z identycznego zestawu. Nie są to dane rzeczywistego sklepu.

Sześć argumentów bez zgadywania ich znaczenia

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

Najpierw podajesz wartość, potem miejsce szukania i zakres wyniku. Czwarty argument określa odpowiedź przy braku dopasowania. Piąty wybiera rodzaj dopasowania, szósty kierunek lub algorytm przeszukiwania. Nawiasy kwadratowe w zapisie składni oznaczają argumenty opcjonalne; nie wpisujesz ich do własnej formuły.

Rozdzielam dwa pytania: co uznaję za dopasowanie i od którego końca szukam? Wartość -1 w piątym argumencie oznacza dopasowanie dokładne albo następny mniejszy element. Ta sama liczba w szóstym argumencie oznacza rozpoczęcie od końca zakresu. Zamiana tych pozycji nie jest drobną zmianą zapisu, tylko zmianą działania.

Przygotuj małą tabelę do sprawdzenia

Wpisz nagłówki w A1:D1, a poniższe sześć rekordów w A2:D7. Pusta komórka D7 ma pozostać naprawdę pusta. Nie wpisuj tam słowa „brak”. Powtórzenie P-102 jest celowe: pokaże różnicę między pierwszym a ostatnim dopasowaniem.

Źródło pokazuje stan produktu, a nie zdarzenia sprzedażowe. Gdyby dwa wpisy P-102 oznaczały różne magazyny, trzeba byłoby uwzględnić magazyn w kluczu albo zsumować odpowiednie rekordy. Sam wybór pierwszego wiersza nie rozwiązuje problemu definicji. W przykładzie traktuję kolejność wyłącznie jako własność tabeli, bez przypisywania jej dat.

Dokładne dopasowanie jest ustawieniem domyślnym

=X.WYSZUKAJ("P-102";$A$2:$A$7;$D$2:$D$7)

Wynik to 25, z pierwszego rekordu P-102. Nie trzeba dopisywać argumentu 0, żeby włączyć dokładne dopasowanie. Nie trzeba też sortować tabeli dla tego zwykłego wyszukiwania. Znaki dolara utrzymują zakresy podczas kopiowania formuły. W arkuszu roboczym zamiast stałego tekstu możesz wskazać komórkę z identyfikatorem.

Sprawdzam, czy zakresy obejmują te same wiersze. Szukanie w A2:A7 i zwracanie D3:D8 przesuwa relację o jeden rekord, choć oba zakresy mają tę samą długość. Taki błąd bywa trudniejszy do zauważenia niż komunikat błędu. Zaczynam więc od produktu, którego wynik potrafię wskazać ręcznie w źródle.

Brak dopasowania nie oznacza zera

=X.WYSZUKAJ("P-999";$A$2:$A$7;$D$2:$D$7;"Brak")

Otrzymasz Brak. Bez czwartego argumentu niedopasowany identyfikator daje #N/D (#N/A w EN). Wolę na początku jawny komunikat niż zero, bo zero sztuk jest prawidłowym wynikiem dla P-104. Ukrycie obu sytuacji pod tą samą liczbą utrudnia kontrolę kompletności połączenia.

Czwarty argument obsługuje brak dopasowania, a nie każdy możliwy problem formuły. Nie naprawi niewłaściwych zakresów ani błędu w dopasowanej komórce. Jeśli potrzebujesz szerszej obsługi błędów, najpierw ustal ich przyczynę; bezrefleksyjne opakowanie całego obliczenia w JEŻELI.BŁĄD może ukryć usterkę źródła.

Wyszukiwanie w lewo i od końca to różne rzeczy

Wyszukiwanie w lewo oznacza, że kolumna wyniku znajduje się przed kolumną szukaną. Tutaj szukam nazwy w B i zwracam identyfikator z A:

=X.WYSZUKAJ("Notebook";$B$2:$B$7;$A$2:$A$7)

Wynik to P-102. To rzeczywiście przykład zwracania kolumny po lewej stronie. Natomiast wyszukiwanie od końca zmienia kolejność sprawdzania rekordów:

=X.WYSZUKAJ("P-102";$A$2:$A$7;$D$2:$D$7;"Brak";0;-1)

Teraz wynik wynosi 31. Szósty argument -1 wybiera ostatnie dopasowanie w zakresie. Nie oznacza automatycznie najnowszego pomiaru ani największej liczby. Jeśli przestawisz wiersze, możesz zmienić wynik. Gdy szukasz najnowszego rekordu, najpierw potrzebujesz daty i jawnej reguły kolejności, w tym rozstrzygnięcia remisów.

Zwróć kilka kolumn jednego rekordu

=X.WYSZUKAJ("P-102";$A$2:$A$7;$C$2:$D$7)

Wpisz formułę na przykład w J2 i pozostaw K2 wolne. Otrzymasz obok siebie Office i 25. Zwracana tablica ma dwie kolumny, więc wynik zajmuje dwie komórki. Nadal jest to jeden dopasowany rekord. Dwa wystąpienia P-102 w źródle nie powodują zwrócenia obu wierszy.

Dokumentacja tablic dynamicznych opisuje rozlewanie wyniku i wymaganie wolnego miejsca. Zablokowany obszar może dać #ROZLANIE! (#SPILL!). Formułę rozlewającą umieść poza obiektem tabeli Excela. Jeśli potrzebujesz wszystkich pasujących rekordów, rozważ FILTRUJ, a przy sumie wielu pozycji odpowiednią agregację.

Dopasowanie przybliżone potrzebuje reguły progów

W F1:G5 przygotuj drugą tabelę. Liczby oznaczają dolne granice umownych kategorii wielkości, bez związku z tabelą stanów magazynowych.

=X.WYSZUKAJ(45;$F$2:$F$5;$G$2:$G$5;"Brak";-1)

Wynik to Medium, bo największy dostępny próg nieprzekraczający 45 wynosi 20. Dla dokładnego progu 50 otrzymasz High. Dla wartości -1 nie ma ani dopasowania dokładnego, ani mniejszego progu, więc odpowiedzią będzie Brak. To sprawdzenie granicy jest częścią projektowania tabeli progów.

=X.WYSZUKAJ(45;$F$2:$F$5;$G$2:$G$5;"Brak";1)

Tutaj wynikiem jest High, odpowiadające następnemu większemu progowi 50. Wybór -1 albo 1 powinien wynikać z definicji przedziału. Nie zmieniam znaku tylko dlatego, że druga odpowiedź bardziej mi odpowiada. Dla dolnych granic kategorii zwykle potrzebuję wariantu z mniejszym progiem.

Symbole wieloznaczne włączasz osobno

=X.WYSZUKAJ("Note*";$B$2:$B$7;$D$2:$D$7;"Brak";2)

Wynik wynosi 25. Piąty argument 2 włącza dopasowanie symbolami wieloznacznymi: gwiazdka pasuje do ciągu znaków, znak zapytania do jednego znaku, a tylda pozwala potraktować taki symbol dosłownie. Bez tego trybu nie zakładaj, że tekst z gwiazdką automatycznie stanie się wzorcem wyszukiwania.

W praktyce sprawdzam, czy wzorzec nie pasuje do zbyt wielu nazw. „Note*” może obejmować różne produkty, a funkcja nadal wybierze pojedynczy rekord według kierunku szukania. Do identyfikowania produktu wolę stabilny kod niż fragment nazwy. Wzorce są użyteczne, kiedy celowo szukam po tekście i znam ograniczenia takiego dopasowania.

Tryb binarny ma twardy warunek

=X.WYSZUKAJ(45;$F$2:$F$5;$G$2:$G$5;"Brak";-1;2)

Dla naszej rosnącej tabeli progów wynik to Medium. Szósty argument 2 oznacza wyszukiwanie binarne z wymaganiem porządku rosnącego; -2 wymaga malejącego. Microsoft ostrzega przed błędnymi wynikami przy nieposortowanej tablicy. Nie włączam tego trybu bez kontroli kolejności.

Sortuję całe powiązane rekordy, nie samą kolumnę klucza. Inaczej próg może przestać odpowiadać właściwej kategorii. W małym ćwiczeniu zostaję przy prostym wyszukiwaniu. Nie podaję obietnicy przyspieszenia bez pomiaru konkretnego skoroszytu; poprawność wyniku jest ważniejsza niż wybranie bardziej zaawansowanej nazwy algorytmu.

Pusta wartość w znalezionym wierszu

P-105 istnieje w źródle, ale D7 jest puste. Argument „Brak” nie zadziała, bo dopasowanie zostało znalezione. Zwykłe odwołanie przez wyszukiwanie do pustej komórki może pokazać zero. Jeśli potrzebujesz odróżnić pusty stan od rzeczywistego zera dla P-104, przekształć zakres wyniku jawnie:

=X.WYSZUKAJ("P-105";$A$2:$A$7;JEŻELI($D$2:$D$7="";"";$D$2:$D$7);"Brak")

Wynikiem jest pusty tekst "", nie fizycznie pusta komórka. Dla P-104 ta sama konstrukcja zwróci liczbę 0, a dla P-999 komunikat Brak. To trzy różne sytuacje. Sposób prezentacji powinien zachować ich znaczenie, zwłaszcza gdy później wynik trafia do kolejnych obliczeń lub kontroli kompletności.

Sprawdź klucz, zanim poprawisz formułę

Przy pozornie brakującym dopasowaniu oglądam wartość źródłową: dodatkowe spacje, liczbę zapisaną jako tekst, niewidoczne znaki i kod z zerami na początku. Nie usuwam automatycznie wszystkich różnic. Jeśli „0012” jest identyfikatorem, zamiana go na liczbę 12 może zniszczyć informację potrzebną do poprawnego połączenia.

Sprawdzam też powtarzalność klucza i zakres danych. Nowy rekord dopisany pod A7 nie znajdzie się w stałym zakresie A2:A7. Obiekt tabeli z odwołaniami strukturalnymi może pomóc utrzymać rosnące źródło, ale nadal potrzebujesz kontroli poprawności klucza. O ograniczeniach starszego sposobu szukania przeczytasz w haśle WYSZUKAJ.PIONOWO.

Na ćwiczenie wybierz P-103, P-104, P-105 i nieistniejące P-999. Sprawdź kolejno zwykły wynik, rzeczywiste zero, pusty tekst oraz brak dopasowania. Potem zamień miejscami dwa wiersze P-102 i porównaj wyszukiwanie od początku i końca. Dzięki temu zobaczysz, które reguły należą do funkcji, a które musisz ustalić dla własnych danych.