VLOOKUP czy XLOOKUP: której funkcji użyć?
Obie funkcje znajdują wartość w jednej kolumnie i zwracają pasującą wartość z tego samego wiersza. X.WYSZUKAJ (Angielski: XLOOKUP) jest nowszą funkcją i usuwa większość ograniczeń WYSZUKAJ.PIONOWO (Angielski: VLOOKUP), ale VLOOKUP nadal jest wszędzie w istniejących skoroszytach.
Ten przewodnik pokazuje różnice, które mają znaczenie, i prosty sposób wyboru.
VLOOKUP: patrz w prawo, licz kolumny
WYSZUKAJ.PIONOWO (Angielski: VLOOKUP) zawsze przeszukuje pierwszą kolumnę podanej tabeli i zwraca wartość z kolumny o numerze, który policzysz. Ostatni argument powinien mieć wartość FALSE dla dopasowania dokładnego; pominięcie go daje dopasowanie przybliżone, które wymaga posortowanych danych.
Przykład: Cena według identyfikatora produktu za pomocą VLOOKUP
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Identyfikator produktu | Produkt | Cena | Stan magazynowy | Identyfikator produktu | P-102 | |
| 2 | P-101 | Lampka biurkowa | 24.5 | 40 | Cena | 149 | |
| 3 | P-102 | Krzesło biurowe | 149 | 12 | |||
| 4 | P-103 | Podstawka pod monitor | 39.9 | 25 | |||
| 5 | P-104 | Klawiatura | 59 | 0 |
Cena to trzecia kolumna zakresu A2:D5. Jeśli ktoś wstawi kolumnę wewnątrz tabeli, liczba 3 może wskazywać na złe dane.
XLOOKUP: dowolny kierunek, bez numerów kolumn
X.WYSZUKAJ (Angielski: XLOOKUP) przyjmuje kolumnę do przeszukania i kolumnę do zwrócenia jako dwa osobne zakresy. Mogą być one w dowolnym miejscu, więc może szukać w lewo, a wstawianie kolumn go nie psuje. Dopasowanie dokładne jest domyślne.
Przykład: Szukanie w lewo za pomocą XLOOKUP
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Identyfikator produktu | Produkt | Cena | Stan magazynowy | ID klawiatury | |
| 2 | P-101 | Lampka biurkowa | 24.5 | 40 | P-104 | |
| 3 | P-102 | Krzesło biurowe | 149 | 12 | ||
| 4 | P-103 | Podstawka pod monitor | 39.9 | 25 | ||
| 5 | P-104 | Klawiatura | 59 | 0 |
Identyfikator jest w kolumnie A, po lewej stronie nazwy z kolumny B. WYSZUKAJ.PIONOWO (Angielski: VLOOKUP) nie może zwrócić kolumny znajdującej się na lewo od kolumny wyszukiwania.
Inne różnice
Brakujące wartości: VLOOKUP zwraca #N/D!, więc owijasz go w JEŻELI.ND (Angielski: IFNA) lub JEŻELI.BŁĄD (Angielski: IFERROR). XLOOKUP ma wbudowany czwarty argument na komunikat.
Kierunek: XLOOKUP może szukać od ostatniego elementu (tryb wyszukiwania -1), co przydaje się przy najnowszym wpisie. VLOOKUP znajduje tylko pierwsze dopasowanie.
Dopasowania przybliżone: VLOOKUP wymaga danych posortowanych rosnąco. Tryby dopasowania -1 i 1 w XLOOKUP działają na danych nieposortowanych.
Wersje: XLOOKUP istnieje w Excelu 2021, Excelu 2024, Microsoft 365 i Excelu dla sieci Web. Nie ma go w Excelu 2019 ani starszych, a plik pokaże tam #NAZWA?. VLOOKUP działa w każdej wersji.
Prosta zasada
Używaj X.WYSZUKAJ (Angielski: XLOOKUP) w nowej pracy, gdy każdy, kto otwiera plik, ma Excel 2021 lub nowszy. Używaj WYSZUKAJ.PIONOWO (Angielski: VLOOKUP) (albo INDEKS (Angielski: INDEX) z PODAJ.POZYCJĘ (Angielski: MATCH)), gdy plik musi się otwierać w starszych wersjach, oraz gdy utrzymujesz istniejące formuły VLOOKUP, które nadal działają.
Typowe błędy
Pominięcie FALSE w VLOOKUP. Wyszukiwanie staje się przybliżone i może zwrócić zły wiersz bez żadnego błędu.
Napraw: Zawsze wpisuj FALSE (lub 0) jako ostatni argument, chyba że chcesz dopasowania przybliżonego.
Wpisane na stałe numery kolumn. Wstawienie lub usunięcie kolumny wewnątrz tabeli zmienia kolumnę, na którą wskazuje liczba.
Napraw: Użyj X.WYSZUKAJ (Angielski: XLOOKUP) albo PODAJ.POZYCJĘ (Angielski: MATCH) do ustalenia numeru kolumny, aby formuła podążała za nagłówkami.
Najczęściej zadawane pytania
Czy VLOOKUP znika?
Nie. Microsoft zachowuje go ze względu na zgodność i nadal jest to najczęstsze wyszukiwanie w istniejących skoroszytach. Warto go znać przy testach i wspólnych plikach.
Czy XLOOKUP jest szybszy od VLOOKUP?
W zwykłych arkuszach różnica nie jest zauważalna. Wybieraj według elastyczności i według tego, jakie wersje Excela muszą otworzyć plik.
Powiązane funkcje
- VLOOKUPVLOOKUP znajduje wartość w pierwszej kolumnie tabeli i zwraca wartość z tego samego wiersza z innej kolumny.
- XLOOKUPXLOOKUP przeszukuje jedną kolumnę lub wiersz i zwraca pasującą wartość z innej kolumny lub wiersza, w dowolnym kierunku, z wbudowanym komunikatem „nie znaleziono”.
- INDEXINDEX zwraca wartość z podanego wiersza i kolumny zakresu.
- MATCHMATCH zwraca pozycję wartości na liście, np.
- IFERRORIFERROR zwraca wartość wybraną przez Ciebie, gdy formuła daje błąd, np.
Więcej: VLOOKUP i XLOOKUP
Przewodnik porównuje obie funkcje i podaje praktyczną zasadę wyboru. Ta część dodaje przypadki, w których jedna działa lepiej od drugiej, jak bezpiecznie przenieść stary VLOOKUP oraz ciche błędy, na które pozwalają obie.
Krok po kroku: konwersja VLOOKUP
Weźmy VLOOKUP, który szuka identyfikatora z A2 w tabeli E2:G50 i zwraca kolumnę 3. Aby go przekonwertować, zamień tabelę na dwa osobne zakresy: kolumnę do przeszukania i kolumnę do zwrócenia. Numer kolumny znika.
=WYSZUKAJ.PIONOWO(A2;E2:G50;3;FAŁSZ)
=X.WYSZUKAJ(A2;E2:E50;G2:G50;"not found")Przed zastąpieniem starej formuły wszędzie przetestuj nową na kilku identyfikatorach, w tym na takim, który nie istnieje.
Błędy, które milczą
- Pominięcie czwartego argumentu VLOOKUP albo wpisanie TRUE oznacza dopasowanie przybliżone. Na nieposortowanych danych zwraca to złą wartość zamiast błędu.
- Wstawienie kolumny do wnętrza tabeli VLOOKUP po cichu zmienia to, na którą kolumnę wskazuje liczba 3.
- Spacja na końcu albo identyfikator zapisany jako tekst po jednej stronie i jako liczba po drugiej sprawiają, że obie funkcje zwracają „nie znaleziono”. Sprawdź typy danych.
- Obie funkcje zwracają pierwsze dopasowanie. Jeśli klucze się powtarzają, pozostałe wiersze nigdy nie zostaną osiągnięte.
Kiedy VLOOKUP nadal jest dobry
X.WYSZUKAJ (Angielski: XLOOKUP) wymaga Excela 2021, Excel 2019 go nie ma, podobnie jak wcześniejsze wersje. Jeśli plik będą otwierać osoby z zespołu na starszej wersji, bezpieczniejszy jest WYSZUKAJ.PIONOWO (Angielski: VLOOKUP) albo para INDEKS (Angielski: INDEX) i PODAJ.POZYCJĘ (Angielski: MATCH). Dopasowania przybliżone i z symbolami wieloznacznymi opisuje [symbole wieloznaczne w Excelu](/guides/wildcards).