W tym poradniku, będziemy patrzeć na to, jak używać funkcji IFERROR i VLOOKUP razem do wychwytywania i obsługi różnych błędów. Dodatkowo, dowiesz się jak wykonywać sekwencyjne vlooki w Excelu poprzez zagnieżdżanie wielu funkcji IFERROR jedna na drugiej.

Excel VLOOKUP i IFERROR – te dwie funkcje mogą być dość trudne do zrozumienia oddzielnie, nie mówiąc już o tym, kiedy są one połączone. W tym artykule znajdziesz kilka łatwych do naśladowania przykładów, które dotyczą typowych zastosowań i jasno ilustrują logikę formuł.

Jeśli nie masz dużego doświadczenia z funkcjami IFERROR i VLOOKUP, dobrym pomysłem może być zapoznanie się najpierw z ich podstawami, korzystając z powyższych linków.

• IFERROR z VLOOKUP do obsługi błędów
  • VLOOKUP i zwróć własny tekst zamiast N/A error
  • VLOOKUP i zwróć pustą komórkę lub zero, jeśli nic nie zostanie znalezione
• Nest IFERROR w ramach VLOOKUP, aby zawsze coś znaleźć
• Sekwencyjne Vlookups z zagnieżdżonymi IFERRORami

IFERROR Formuła VLOOKUP do obsługi #N/A i innych błędów

Gdy Excel Vlookup nie znajdzie wartości odnośnika, wyrzuca błąd #N/A, jak poniżej:

W zależności od potrzeb biznesowych możesz chcieć zamaskować błąd za pomocą własnego tekstu, zera lub pustej komórki.

Przykład 1. Iferror Formuła Vlookup zastępująca wszystkie błędy własnym tekstem

Jeśli chcesz zastąpić standardową notację błędu swoim własnym tekstem, zawiń swoją formułę VLOOKUP w IFERROR i wpisz dowolny tekst w drugim argumencie (value_if_error), na przykład „Nie znaleziono”:

Przy wartości lookup w B2 w tabeli Main i zakresie A2:B4 w tabeli Lookup formuła przyjmuje następujący kształt:

=IFERROR(VLOOKUP(B2,'Lookup table'!$A:$B, 2, FALSE), "Not found")

Zrzut ekranu poniżej pokazuje naszą formułę Excel IFERROR VLOOKUP w akcji:

Wynik wygląda znacznie bardziej zrozumiale i znacznie mniej onieśmielająco, prawda?

W podobny sposób możesz użyć INDEX MATCH razem z IFERROR:

=IFERROR(INDEX('Lookup table'!$B:$B,MATCH(B2,'Lookup table'!$A:$A,0)), "Not found")

Formuła IFERROR INDEX MATCH jest szczególnie przydatna, gdy chcesz wyciągnąć wartości z kolumny, która leży na lewo od kolumny szukanej (left lookup), i zwrócić własny tekst, gdy nic nie zostanie znalezione.

Przykład 2. IFERROR z VLOOKUP, aby zwrócić puste lub 0, jeśli nic nie zostanie znalezione

Jeśli nie chcesz nic pokazywać, gdy wartość lookup nie zostanie znaleziona, niech IFERROR wyświetli pusty łańcuch („”):

W naszym przykładzie formuła przebiega następująco:

=IFERROR(VLOOKUP(B2,'Lookup table'!$A:$B, 2, FALSE), "")

Jak widać, nie zwraca ona nic, gdy wartości lookup nie ma na liście wyszukiwania.

Jeśli chciałbyś zastąpić błąd wartością zerową, wstaw 0 do ostatniego argumentu:

=IFERROR(VLOOKUP(B2,'Lookup table'!$A:$B, 2, FALSE), 0)

Słowo przestrogi! Funkcja IFERROR w Excelu wyłapuje wszystkie rodzaje błędów, nie tylko #N/A. Czy to dobrze, czy źle? Wszystko zależy od celu jaki chcesz osiągnąć. Jeśli chcesz zamaskować wszystkie możliwe błędy, IFERROR Vlookup jest drogą do zrobienia. Ale może to być nierozsądna technika w wielu sytuacjach.

Na przykład, jeśli utworzyłeś nazwany zakres dla danych tabeli i błędnie wpisałeś tę nazwę w formule Vlookup, IFERROR złapie błąd #NAME? i zastąpi go „Nie znaleziono” lub innym tekstem, który dostarczysz. W rezultacie, możesz nigdy nie wiedzieć, że twoja formuła dostarcza błędnych wyników, chyba że sam zauważysz literówkę. W takim przypadku, bardziej rozsądnym podejściem byłoby przechwytywanie tylko błędów #N/A. W tym celu użyj formuły IFNA Vlookup w Excelu dla Office 365, Excel 209, Excel 2016 i Excel 2013, IF ISNA VLOOKUP we wszystkich wersjach Excela.

Podsumowanie jest takie: bądź bardzo ostrożny przy wyborze towarzysza dla swojej formuły VLOOKUP 🙂

Zagnieżdż IFERROR wewnątrz VLOOKUP, aby zawsze coś znaleźć

Wyobraź sobie następującą sytuację: szukasz określonej wartości na liście i nie znajdujesz jej. Co masz do wyboru? Albo dostać błąd N/A, albo pokazać własny komunikat. Właściwie jest jeszcze trzecia opcja – jeśli twój podstawowy vlookup się potknie, to poszukaj czegoś innego, co na pewno tam jest!

Przechodząc dalej do naszego przykładu, stwórzmy jakiś dashboard dla naszych użytkowników, który pokaże im numer wewnętrzny konkretnego biura. Coś w tym stylu:

Więc, jak wyciągnąć numer wewnętrzny z kolumny B na podstawie numeru biura w D2? Za pomocą zwykłej formuły Vlookup:

=VLOOKUP($D,$A:$B,2,FALSE)

I to będzie działać dobrze tak długo, jak długo użytkownicy wprowadzają prawidłowy numer w D2. Ale co jeśli użytkownik wprowadzi jakąś liczbę, która nie istnieje? W tym przypadku, niech zadzwoni do centrali! W tym celu umieszczamy powyższą formułę w argumencie value IFERROR, a w argumencie value_if_error umieszczamy kolejny Vlookup.

Pełna formuła jest trochę długa, ale działa doskonale:

=IFERROR(VLOOKUP("office "&$D,$A:$B,2,FALSE),VLOOKUP("central office",$A:$B,2,FALSE))

Jeżeli numer biura zostanie znaleziony, użytkownik otrzyma odpowiedni numer wewnętrzny:

Jeżeli numer biura nie zostanie znaleziony, wyświetlony zostanie numer wewnętrzny centrali:

Aby uczynić formułę nieco bardziej kompaktową, można zastosować inne podejście:

Po pierwsze, sprawdź, czy numer w D2 jest obecny w kolumnie lookup (zauważ, że ustawiliśmy col_index_num na 1, aby formuła szukała i zwracała wartość z kolumny A): VLOOKUP(D2,$A$2:$B$7,1,FALSE)

Jeśli podany numer urzędu nie zostanie znaleziony, to szukamy ciągu „urząd centralny”, który na pewno znajduje się na liście lookup. W tym celu zawijamy pierwszy VLOOKUP w IFERROR i zagnieżdżamy całą tę kombinację wewnątrz innej funkcji VLOOKUP:

=VLOOKUP(IFERROR(VLOOKUP(D2,$A:$B,1,FALSE),"central office"),$A:$B,2)

Cóż, nieco inna formuła, ten sam wynik:

Ale jaki jest powód szukania „central office”, możesz mnie zapytać. Dlaczego nie podać numeru wewnętrznego bezpośrednio w IFERROR? Ponieważ rozszerzenie może się zmienić w pewnym momencie w przyszłości. Jeśli tak się stanie, będziesz musiał zaktualizować swoje dane tylko raz w tabeli źródłowej, nie martwiąc się o aktualizację każdej z formuł VLOOKUP.

Jak wykonać sekwencyjne VLOOKUPy w Excelu

W sytuacjach, w których musisz wykonać tak zwane sekwencyjne lub łańcuchowe VLOOKUPy w Excelu w zależności od tego, czy wcześniejsze wyszukiwanie zakończyło się powodzeniem, czy niepowodzeniem, zagnieżdż dwie lub więcej funkcji IFERROR, aby uruchomić Vlookupy jeden po drugim:

Formuła ta działa zgodnie z następującą logiką:

Jeśli pierwszy VLOOKUP niczego nie znajdzie, pierwszy IFERROR przechwytuje błąd i uruchamia kolejny VLOOKUP. Jeśli drugi VLOOKUP się nie powiedzie, drugi IFERROR łapie błąd i uruchamia trzeci VLOOKUP, i tak dalej. Jeśli wszystkie Vlookups potknie, ostatni IFERROR zwraca wiadomość.

Ta zagnieżdżona formuła IFERROR jest szczególnie przydatna, gdy trzeba Vlookup w wielu arkuszach, jak pokazano w poniższym przykładzie.

Powiedzmy, że masz trzy listy jednorodnych danych w trzech różnych arkuszach (numery biur w tym przykładzie), i chcesz uzyskać rozszerzenie dla pewnego numeru.

Zakładając, że wartość odnośnika znajduje się w komórce A2 w bieżącym arkuszu, a zakres odnośnika to A2:B5 w 3 różnych arkuszach (Północ, Południe i Zachód), poniższa formuła działa jak należy:

=IFERROR(VLOOKUP(A2,North!$A:$B,2,FALSE), IFERROR(VLOOKUP(A2,South!$A:$B,2,FALSE), IFERROR(VLOOKUP(A2,West!$A:$B,2,FALSE),"Not found")))

Więc nasza formuła „chained Vlookups” przeszukuje wszystkie trzy arkusze w kolejności, w jakiej zagnieździliśmy je w formule, i przynosi pierwsze dopasowanie, które znajdzie:

Tak właśnie używasz IFERROR z VLOOKUP w Excelu. Dziękuję za przeczytanie i mam nadzieję do zobaczenia na naszym blogu w przyszłym tygodniu!

Dostępne pliki do pobrania

Excel IFERROR VLOOKUP przykłady formuły

Możesz być zainteresowany również

• Excel IFERROR funkcja z przykładami formuły
• Używanie IF z VLOOKUP w Excelu
• Excel VLOOKUP samouczek dla początkujących

.