Dlaczego VLOOKUP czasem zawodzi

VLOOKUP jest niezawodną funkcją, ale zawodzi w kilku przewidywalnych sytuacjach. Większość błędów przestaje być tajemnicza, gdy zna się odpowiednie zasady.

W tej lekcji poznasz typowe przyczyny nieprawidłowego wyszukiwania oraz dokładne sposoby ich naprawy. Dzięki tej wiedzy mylące błędy #N/A i #REF! zamienią się w szybkie i łatwe do usunięcia problemy.

VLOOKUP może przeszukiwać tylko skrajną lewą kolumnę swojego table_array i zwracać wartości z kolumn znajdujących się po jej prawej stronie. Nie potrafi wyszukać wartości i zwrócić danych znajdujących się po jej lewej stronie.

Jeśli identyfikatory znajdują się w kolumnie C, a potrzebna nazwa w kolumnie A, VLOOKUP nie może sięgnąć w lewo. Masz dwie możliwości: zmienić kolejność kolumn tak, aby kolumna wyszukiwania była pierwsza, albo użyć INDEX-MATCH lub XLOOKUP, które mogą wyszukiwać w dowolnym kierunku.

Wartość col_index_num liczy się od lewej strony table_array, a nie od arkusza. Częstym błędem jest użycie jako liczby litery kolumny arkusza.

Jeśli zakres to C1:F10, a chcesz odwołać się do kolumny F, jest to 4. kolumna zakresu, więc indeks wynosi 4, a nie 6. Liczenie od niewłaściwej strony zwróci nieprawidłowe pole albo — jeśli liczba przekracza szerokość zakresu — błąd #REF!.

Jeśli col_index_num jest większy niż liczba kolumn w table_array, VLOOKUP zwraca błąd #REF!.

Na przykład żądanie kolumny 5 z trzykolumnowego zakresu A1:C10 jest niemożliwe:

Napraw ten problem, rozszerzając table_array tak, aby obejmował potrzebną kolumnę, albo poprawiając indeks na rzeczywisty numer kolumny mieszczący się w zakresie.

Pominięcie czwartego argumentu powoduje domyślne użycie wartości TRUE (dopasowanie przybliżone). W przypadku nieposortowanej listy funkcja bez ostrzeżenia zwróci niewłaściwą wartość z sąsiedniej pozycji zamiast błędu, przez co problem trudno zauważyć.

Rozwiązanie jest proste i warto wyrobić sobie ten nawyk: zawsze dodawaj FALSE przy wyszukiwaniu dokładnym.

Wartość wyszukiwania "A100" nie będzie pasować do "A100 " zawierającej spację na końcu. Importowane dane są pełne takich niewidocznych różnic.

Objaw jest następujący: wartość wyraźnie istnieje, a mimo to otrzymujesz #N/A. Oczyść obie strony za pomocą funkcji TRIM, aby usunąć zbędne spacje:

Jeśli szukana wartość jest liczbą 100, ale tabela przechowuje kody jako tekst "100" (lub odwrotnie), wartości nie będą do siebie pasować i otrzymasz #N/A.

Poszukaj małego zielonego trójkąta lub liczb wyrównanych do lewej — to sygnały, że są zapisane jako tekst. Napraw problem, konwertując wartości: otocz tekst funkcją VALUE(), aby zamienić go na liczbę, albo połącz liczbę z pustym ciągiem za pomocą &"", aby zamienić ją na tekst. Dzięki temu obie strony będą miały ten sam typ.

Jeśli zapomnisz zablokować table_array, kopiowanie formuły w dół przesunie zakres poza dane. Zakres A1:C100 w wierszu 2 zmieni się w A2:C101 w wierszu 3, a następnie w A3:C102, pomijając po drodze kolejne wiersze.

Napraw to za pomocą odwołań bezwzględnych, aby tabela pozostała nieruchoma, a przesuwała się tylko wartość wyszukiwania:

Każdy błąd wskazuje na określoną przyczynę:

• #N/A - nie znaleziono wartości (niezgodność, spacje, nieprawidłowy typ albo rzeczywisty brak wartości)
• #REF! - col_index_num jest większy niż zakres albo odwołana komórka została usunięta
• #VALUE! - argument ma nieprawidłowy typ, na przykład indeks kolumny jest ujemny lub równy zero
• #NAME? - nazwa funkcji jest błędnie zapisana, na przykład VLOOKP

Jeśli dopasujesz błąd do jego znaczenia, połowa problemu będzie już rozwiązana.

Podczas debugowania możesz również opakować funkcję wyszukiwania, aby użytkownicy widzieli jasny komunikat zamiast surowego błędu. IFERROR przechwytuje każdy błąd i zwraca w jego miejsce podany tekst.

Nie naprawia to podstawowej przyczyny, dlatego używaj tej funkcji dopiero po zrozumieniu, dlaczego wyszukiwanie się nie powiodło. Zbyt wczesne ukrywanie błędów może zamaskować rzeczywiste problemy z danymi.

Gdy wyszukiwanie nie działa prawidłowo, przejdź przez tę krótką listę kontrolną:

• Czy szukana wartość znajduje się w pierwszej kolumnie zakresu?
• Czy wartość col_index_num jest liczona od lewej krawędzi zakresu i mieści się w jego szerokości?
• Czy dodano FALSE, aby uzyskać dopasowanie dokładne?
• Czy obie strony mają ten sam typ (tekst lub liczba) i nie zawierają zbędnych spacji?
• Czy table_array jest zablokowany za pomocą znaków dolara?

Przejście przez tę listę od góry do dołu rozwiązuje zdecydowaną większość problemów z wyszukiwaniem w ciągu kilku sekund.

Zdiagnozuj tę nieprawidłowo działającą funkcję wyszukiwania.

Najczęstsze przyczyny i sposoby ich naprawy:

• Ograniczenie lewej kolumny - zmień kolejność kolumn albo użyj INDEX-MATCH / XLOOKUP
• Nieprawidłowy lub zbyt duży col_index_num - licz od lewej krawędzi zakresu; rozszerz zakres
• Brak FALSE - w przypadku identyfikatorów zawsze ustawiaj dopasowanie dokładne
• Spacje i różnica między tekstem a liczbą - oczyść dane za pomocą TRIM, konwertuj za pomocą VALUE lub &""
• Odblokowana tabela - użyj $, aby zakres pozostał na swoim miejscu

Odczytaj kod błędu, dopasuj go do przyczyny i zastosuj rozwiązanie. Masz już kompletny zestaw narzędzi do wykonywania niezawodnych wyszukiwań.