Wybierając między XLOOKUP a VLOOKUP, użyj XLOOKUP, jeśli wszyscy, którzy otwierają plik, mają Excela dla Microsoft 365, Excela 2021 lub nowszego albo Excela dla internetu. Robi wszystko, co VLOOKUP, z mniejszą liczbą pomyłek: domyślnie szuka dokładnego dopasowania, potrafi szukać w lewo i nie psuje się po wstawieniu kolumny. VLOOKUP stosuj tylko wtedy, gdy skoroszyt musi działać w Excelu 2019 lub starszym.
Kompatybilność to główne zastrzeżenie. XLOOKUP nie istnieje w Excelu 2016 ani 2019, więc plik pełen formuł XLOOKUP pokazuje błędy #NAME? każdemu, kto nadal korzysta z tych wersji.
W polskiej wersji Excela funkcje mają polskie nazwy: VLOOKUP to WYSZUKAJ.PIONOWO, HLOOKUP to WYSZUKAJ.POZIOMO, XLOOKUP to X.WYSZUKAJ, a argumenty rozdzielasz średnikami. W tym artykule formuły podajemy w formie polskiej i angielskiej tam, gdzie to ułatwia czytanie; w angielskim Excelu użyj nazw angielskich i przecinków.
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Szukanie w lewo (zwracanie kolumny na lewo od kolumny wyszukiwania) | Nie | Tak |
| Domyślny typ dopasowania | Przybliżone (chyba że dodasz FAŁSZ) | Dokładne |
| Odporność na wstawianie lub usuwanie kolumn | Nie, numer kolumny pozostaje stały | Tak, zakresy dostosowują się |
| Własny komunikat „nie znaleziono” | Wymaga JEŻELI.ND lub JEŻELI.BŁĄD | Wbudowany (if_not_found) |
| Zwracanie kilku kolumn naraz | Niełatwo | Tak, wyniki się rozlewają |
| Szukanie od ostatniego dopasowania w górę | Nie | Tak (search_mode -1) |
| Dopasowanie przybliżone | Dane muszą być posortowane rosnąco | Następna mniejsza lub większa wartość, bez sortowania |
Symbole wieloznaczne (*, ?) | Tak, przy dopasowaniu dokładnym | Tak, z match_mode 2 |
| Wyszukiwanie poziome | Nie, do tego służy WYSZUKAJ.POZIOMO | Tak, ta sama funkcja |
| Szybkość | Brak praktycznej różnicy w typowych skoroszytach | Taka sama, plus opcjonalne wyszukiwanie binarne na posortowanych danych |
| Dostępność | Każda wersja Excela | Microsoft 365, Excel 2021, Excel 2024, Excel dla internetu, aplikacje mobilne |
Składnia obok siebie
Wersja polska:
=WYSZUKAJ.PIONOWO(szukana_wartość; tablica; nr_indeksu_kolumny; [przeszukiwany_zakres])
=X.WYSZUKAJ(szukana_wartość; przeszukiwana_tablica; tablica_zwracana; [jeśli_nie_znaleziono]; [tryb_dopasowania]; [tryb_wyszukiwania])
Wersja angielska:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Główna różnica dotyczy tego, jak mówisz Excelowi, gdzie jest odpowiedź. VLOOKUP przyjmuje całą tabelę i numer kolumny liczony od lewej krawędzi tej tabeli. XLOOKUP przyjmuje dwa osobne zakresy: kolumnę, w której szukasz, i kolumnę, z której zwracasz wynik.
To samo wyszukiwanie zapisane na oba sposoby
Oto mała lista produktów w A1:D6 z identyfikatorem produktu wpisanym w G2 (dane przykładowe pozostawiliśmy w oryginalnej postaci, tak jak na zrzutach ekranu):
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product ID | Product | Category | Price |
| 2 | P-101 | Wireless Mouse | Accessories | $24.99 |
| 3 | P-102 | USB-C Hub | Accessories | $39.50 |
| 4 | P-103 | Laptop Stand | Office | $45.00 |
| 5 | P-104 | Webcam HD | Video | $59.00 |
| 6 | P-105 | Desk Lamp | Office | $32.75 |
Aby znaleźć cenę produktu P-104:
=WYSZUKAJ.PIONOWO(G2; A2:D6; 4; FAŁSZ)
=X.WYSZUKAJ(G2; A2:A6; D2:D6)
(W angielskim Excelu: =VLOOKUP(G2, A2:D6, 4, FALSE) i =XLOOKUP(G2, A2:A6, D2:D6).)
Obie zwracają $59.00. VLOOKUP przeszukuje pierwszą kolumnę zakresu A2:D6 i zwraca czwartą kolumnę; FAŁSZ wymusza dopasowanie dokładne. XLOOKUP przeszukuje A2:A6 i zwraca pasującą komórkę z D2:D6, a dopasowanie dokładne jest ustawieniem domyślnym.
Jeśli będziesz kopiować formułę w dół kolumny, zablokuj tabelę odwołaniami bezwzględnymi, na przykład =X.WYSZUKAJ(G2; $A$2:$A$6; $D$2:$D$6). Naciśnij F4 w Windows lub Cmd+T na Macu, gdy kursor jest w odwołaniu, aby dodać znaki $.
Dwie klasyczne pułapki VLOOKUP
1. Zapomniany argument FAŁSZ (lub 0)
Ostatni argument VLOOKUP jest opcjonalny, a gdy go pominiesz, Excel używa dopasowania przybliżonego. Przy dopasowaniu przybliżonym VLOOKUP zakłada, że pierwsza kolumna jest posortowana, i bez żadnego ostrzeżenia zwraca najbliższą wartość mniejszą lub równą szukanej.
Wypróbuj =WYSZUKAJ.PIONOWO("P-110"; A2:D6; 4) na powyższej tabeli. P-110 nie istnieje, ale zamiast #N/D dostajesz $32.75, czyli cenę P-105, bo „P-105” jest najbliższą mniejszą wartością. Na nieposortowanych danych wyniki mogą być jeszcze bardziej przypadkowe.
Zawsze kończ VLOOKUP z dopasowaniem dokładnym argumentem FAŁSZ lub 0. XLOOKUP unika tego problemu, bo używa dopasowania dokładnego, dopóki nie zażądasz inaczej.
2. Numer kolumny wpisany na sztywno
Cyfra 4 w formule VLOOKUP to po prostu liczba. Jeśli ktoś wstawi kolumnę Supplier (dostawca) między Product a Category, tabela zmieni się w A:E, Price przeniesie się do kolumny E, a formuła nadal zwróci czwartą kolumnę, którą jest teraz Category. Zobaczysz „Video” zamiast $59.00.
XLOOKUP wskazuje samą kolumnę z ceną (D2:D6), więc Excel automatycznie zaktualizuje ją do E2:E6 i odpowiedź pozostanie poprawna.
Co potrafi XLOOKUP, a czego nie potrafi VLOOKUP
Szukanie w lewo
VLOOKUP może zwracać tylko kolumny na prawo od kolumny wyszukiwania. XLOOKUP nie dba o kierunek. Aby znaleźć identyfikator produktu dla nazwy produktu w G2:
=X.WYSZUKAJ(G2; B2:B6; A2:A6)
Gdy w G2 jest „Desk Lamp”, formuła zwraca P-105, mimo że kolumna A leży na lewo od kolumny B.
Przyjazny komunikat, gdy nic nie znaleziono
=X.WYSZUKAJ(G2; A2:A6; D2:D6; "Nie znaleziono")
Odpowiednik z VLOOKUP wymaga opakowania: =JEŻELI.ND(WYSZUKAJ.PIONOWO(G2; A2:D6; 4; FAŁSZ); "Nie znaleziono").
Zwracanie kilku kolumn jedną formułą
=X.WYSZUKAJ(G2; A2:A6; B2:D6)
Dla P-104 formuła rozleje Webcam HD, Video i $59.00 do trzech sąsiednich komórek. Zostaw te komórki puste, bo inaczej pojawi się błąd #SPILL!.
Znajdowanie ostatniego dopasowania zamiast pierwszego
W dzienniku, w którym ten sam klient pojawia się wiele razy, ustaw search_mode na -1, aby szukać od dołu do góry i otrzymać najnowszy wpis:
=X.WYSZUKAJ(G2; A2:A500; C2:C500; "Nie znaleziono"; 0; -1)
Dopasowanie przybliżone bez sortowania
W przypadku progów cenowych lub stawek prowizji dopasowanie przybliżone w VLOOKUP działa tylko wtedy, gdy pierwsza kolumna jest posortowana rosnąco. Wartości match_mode -1 (dopasowanie dokładne lub następny mniejszy element) i 1 (dopasowanie dokładne lub następny większy element) w XLOOKUP nie wymagają posortowanych danych. Przy minimalnych kwotach zamówienia w J2:J5 i rabatach w K2:K5:
=WYSZUKAJ.PIONOWO(B2; J2:K5; 2; PRAWDA)
=X.WYSZUKAJ(B2; J2:J5; K2:K5; ; -1)
Symbole wieloznaczne
Obie funkcje obsługują * i ?, ale XLOOKUP wymaga match_mode 2, aby traktować je jako symbole wieloznaczne:
=WYSZUKAJ.PIONOWO("Desk*"; B2:D6; 3; FAŁSZ)
=X.WYSZUKAJ("Desk*"; B2:B6; D2:D6; ; 2)
Obie zwracają $32.75 dla Desk Lamp.
INDEX/MATCH: klasyczna alternatywa
Przed XLOOKUP standardowym sposobem na ograniczenia VLOOKUP było połączenie INDEKS z PODAJ.POZYCJĘ (INDEX/MATCH). Szuka w lewo, jest odporne na wstawione kolumny i działa w każdej wersji Excela, więc to najlepszy wybór dla plików, które muszą otwierać się w Excelu 2019 lub starszym:
=INDEKS(D2:D6; PODAJ.POZYCJĘ(G2; A2:A6; 0))
=INDEKS(A2:A6; PODAJ.POZYCJĘ(G2; B2:B6; 0))
Pierwsza zwraca cenę dla identyfikatora z G2; druga to przykład szukania w lewo. Zero w PODAJ.POZYCJĘ oznacza dopasowanie dokładne i tak jak FAŁSZ w VLOOKUP łatwo o nim zapomnieć. XLOOKUP to po prostu krótsza, bezpieczniejsza wersja tego samego pomysłu.
Jak zamienić formuły VLOOKUP na XLOOKUP
Nie ma wbudowanego konwertera, ale tłumaczenie jest mechaniczne:
- Znajdź swoje formuły VLOOKUP: naciśnij
Ctrl+F(Cmd+Fna Macu), wpisz WYSZUKAJ.PIONOWO, kliknij Opcje, jeśli dodatkowe ustawienia są ukryte, ustaw Szukaj w na Formuły i kliknij Znajdź wszystko. - Pierwszy argument (szukaną wartość) zostaw bez zmian.
- Zastąp tabelę jej pierwszą kolumną jako przeszukiwaną tablicą (
lookup_array). - Zastąp numer kolumny odpowiednią kolumną jako tablicą zwracaną (
return_array). Kolumna 5 tabeli zaczynającej się w kolumnie A to kolumna E. - Usuń
FAŁSZ. Jeśli oryginał kończył się argumentemPRAWDAalbo nie miał ostatniego argumentu, dodajmatch_mode-1, aby zachować zachowanie „następna mniejsza”.
Na przykład:
=WYSZUKAJ.PIONOWO(A2; Products!$A$2:$F$500; 5; FAŁSZ)
=X.WYSZUKAJ(A2; Products!$A$2:$A$500; Products!$E$2:$E$500)
Przetestuj kilka wyników względem starej formuły, zanim przekonwertujesz cały arkusz. VLOOKUP z dopasowaniem przybliżonym na nieposortowanych danych mógł przez cały czas zwracać błędne wartości, więc odpowiedzi XLOOKUP mogą zasadnie się różnić.
Co się dzieje, gdy plik z XLOOKUP otworzy się w Excelu 2016 lub 2019
Skoroszyt się otwiera, ale te wersje nie znają funkcji. Na pasku formuły pojawia się ona jako =_xlfn.XLOOKUP(...). Komórki mogą z początku nadal pokazywać ostatnio zapisane wyniki, ale gdy tylko formuła się przeliczy, zwraca #NAME? (w polskim Excelu #NAZWA?).
Jeśli udostępniasz pliki osobom ze starszymi wersjami:
- użyj zamiast tego INDEX/MATCH lub VLOOKUP albo
- wyślij kopię z wynikami wklejonymi jako wartości (Kopiuj → Wklej specjalnie → Wartości).
To samo dotyczy innych nowszych funkcji, takich jak FILTRUJ, UNIKATOWE i X.PODAJ.POZYCJĘ.
XLOOKUP w Arkuszach Google
Arkusze Google (Google Sheets) też mają XLOOKUP, z taką samą kolejnością argumentów: =XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]). Formuły z tego artykułu działają tam w postaci angielskiej bez zmian, więc XLOOKUP to bezpieczny wybór dla plików wędrujących między Excelem a Sheets.
Dlaczego wyszukiwanie zwraca #N/D lub błędną wartość
- Zbędne spacje. „P-104 ” ze spacją na końcu nie pasuje do „P-104”. Oczyść dane funkcją
USUŃ.ZBĘDNE.ODSTĘPY. - Liczby zapisane jako tekst. Identyfikator wpisany jako tekst nie pasuje do tego samego identyfikatora zapisanego jako liczba. Poszukaj zielonego trójkąta w rogu komórki i zamień kolumnę na jeden typ.
- Brak FAŁSZ w VLOOKUP. Dostajesz wiarygodną, ale błędną odpowiedź zamiast
#N/D. - Zakresy, które przesunęły się po skopiowaniu formuły. Użyj odwołań bezwzględnych
$dla tabeli wyszukiwania. #ADR!z VLOOKUP. Numer kolumny jest większy niż liczba kolumn w tabeli (angielskie#REF!).#ARG!z XLOOKUP. Zakres wyszukiwania i zakres zwracany mają różne rozmiary, na przykład A2:A6 i D2:D60 (angielskie#VALUE!).#NAZWA?Nazwa funkcji jest błędnie wpisana albo plik jest otwarty w wersji bez XLOOKUP (angielskie#NAME?).
Najczęściej zadawane pytania
Czy XLOOKUP jest lepszy niż VLOOKUP? Tak, w prawie każdym zastosowaniu. Ma domyślne dopasowanie dokładne, szuka w dowolnym kierunku, nie psuje się po przesunięciu kolumn i radzi sobie z brakującymi wartościami bez opakowania. Jedyną realną zaletą VLOOKUP jest to, że działa w każdej wersji Excela.
Czy XLOOKUP jest szybszy niż VLOOKUP? W codziennych skoroszytach nie zauważysz różnicy. Na bardzo dużych posortowanych listach tryby wyszukiwania binarnego XLOOKUP (search_mode 2 lub -2) mogą przyspieszyć działanie, ale zwracają błędne wyniki, jeśli dane nie są posortowane.
Dlaczego XLOOKUP pokazuje #NAZWA? na moim komputerze? Twoja wersja Excela nie zawiera XLOOKUP. Jest w Microsoft 365, Excelu 2021 i nowszych oraz w Excelu dla internetu, ale nie w Excelu 2019 ani 2016. Użyj zamiast tego INDEX/MATCH.
Czy XLOOKUP zastępuje też HLOOKUP? Tak. Jeśli zakresy wyszukiwania i zwracany są wierszami zamiast kolumnami, XLOOKUP przeszukuje poziomo z tą samą składnią.
Czy XLOOKUP może zwrócić wszystkie dopasowania, a nie tylko pierwsze? Nie, zwraca jedno dopasowanie (lub jeden wiersz kolumn). Aby uzyskać każdy pasujący wiersz, użyj FILTRUJ, na przykład =FILTRUJ(A2:D6; C2:C6="Office").
Czy nadal warto uczyć się VLOOKUP? Warto go rozpoznawać, bo znajdziesz go w starszych skoroszytach i szablonach. W nowych formułach ucz się XLOOKUP i pamiętaj o INDEX/MATCH dla plików, które muszą otwierać się w starszym Excelu.
Niech AI napisze wyszukiwanie za Ciebie
Formuły wyszukujące to miejsce, w którym drobne szczegóły, takie jak brakujące FAŁSZ albo przesunięty zakres, powodują najwięcej cichych błędów. CoreGPT for Excel pozwala opisać, czego potrzebujesz, na przykład „znajdź cenę dla identyfikatora produktu w G2” lub „oznacz każdy wiersz jako powyżej lub poniżej celu”, i wpisuje formułę do arkusza wraz z krótkim wyjaśnieniem. Można zacząć za darmo, a narzędzie działa w Excelu dla Windows, Maca i internetu.
CoreGPTUżyj tej formuły i przeciągnij ją w dół:
=IF(C2>=D2,"Powyżej","Poniżej")Wypróbuj: CoreGPT for Excel
Powiązane artykuły:
