Când utilizați formula VLOOKUP în Excel, uneori puteți ajunge la eroarea urâtă # N / A. Acest lucru se întâmplă atunci când formula dvs. nu poate găsi valoarea de căutare.
În acest tutorial, vă voi arăta diferite moduri de a utiliza IFERROR cu VLOOKUP pentru a gestiona aceste erori # N / A care apar în foaia de lucru.
Utilizarea combinației IFERROR cu VLOOKUP vă permite să arătați ceva semnificativ în locul erorii # N / A (sau orice altă eroare în acest sens).
Înainte de a intra în detalii despre utilizarea acestei combinații, să trecem mai întâi rapid la funcția IFERROR și să vedem cum funcționează.
Funcția IFERROR explicată
Cu funcția IFERROR, puteți specifica ce ar trebui să se întâmple în cazul în care o formulă sau o referință de celulă returnează o eroare.
Iată sintaxa funcției IFERROR.
= IFERROR (valoare, valoare_if_error)
- valoare - acesta este argumentul verificat pentru eroare. În majoritatea cazurilor, este fie o formulă, fie o referință de celulă. Atunci când se utilizează VLOOKUP cu IFERROR, formula VLOOKUP ar fi acest argument.
- value_if_error - aceasta este valoarea returnată dacă există o eroare. Următoarele tipuri de erori evaluate: # N / A, #REF !, # DIV / 0 !, #VALUE !, #NUM !, #NAME ?, și #NULL !.
Cauzele posibile ale VLOOKUP care returnează o eroare # N / A
Funcția VLOOKUP poate returna o eroare # N / A din oricare dintre următoarele motive:
- Valoarea de căutare nu se găsește în matricea de căutare.
- Există un spațiu principal, final sau dublu în valoarea de căutare (sau în matricea de tabele).
- Există o eroare de ortografie în valoarea de căutare sau în valorile din matricea de căutare.
Puteți gestiona toate aceste cauze de eroare cu combinația IFERROR și VLOOKUP. Cu toate acestea, ar trebui să țineți cont de cauza # 2 și # 3 și să le corectați în datele sursă în loc să lăsați IFERROR să le gestioneze.
Notă: IFERROR ar trata o eroare, indiferent de cauza. Dacă doriți doar să tratați erorile cauzate de faptul că VLOOKUP nu poate găsi valoarea de căutare, utilizați în schimb IFNA. Acest lucru vă va asigura că alte erori decât # N / A nu sunt tratate și că puteți investiga aceste alte erori.
Puteți trata spațiile principale, finale și duble folosind funcția TRIM.
Înlocuirea erorii VLOOKUP # N / A cu text semnificativ
Să presupunem că aveți un set de date așa cum se arată mai jos:
După cum puteți vedea, formula VLOOKUP returnează o eroare, deoarece valoarea de căutare nu se află în listă. Căutăm să obținem scorul pentru Glen, care nu este în tabelul scorurilor.
Deși acesta este un set de date foarte mic, este posibil să obțineți seturi de date uriașe în care trebuie să verificați apariția multor articole. Pentru fiecare caz în care valoarea nu este găsită, veți primi o eroare # N / A.
Iată formula pe care o puteți utiliza pentru a obține ceva semnificativ în loc de eroarea # N / A.
= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 10,2,0), „Nu a fost găsit”)
Formula de mai sus returnează textul „Nu a fost găsit” în loc de eroarea # N / A. De asemenea, puteți utiliza aceeași formulă pentru a returna textul gol, zero sau orice alt text semnificativ.
Îmbogățire VLOOKUP cu funcția IFERROR
În cazul în care utilizați VLOOKUP și tabelul dvs. de căutare este fragmentat pe aceeași foaie de lucru sau foi de lucru diferite, trebuie să verificați valoarea VLOOKUP prin toate aceste tabele.
De exemplu, în setul de date prezentat mai jos, există două tabele separate cu numele studenților și scorurile.
Dacă trebuie să găsesc scorul Grace în acest set de date, trebuie să folosesc funcția VLOOKUP pentru a verifica primul tabel și, dacă valoarea nu se găsește în acesta, verificați al doilea tabel.
Iată formula IFERROR imbricată pe care o pot folosi pentru a căuta valoarea:
= IFERROR (VLOOKUP (G3, $ A $ 2: $ B $ 5,2,0), IFERROR (VLOOKUP (G3, $ D $ 2: $ E $ 5,2,0), „Nu a fost găsit”))
Utilizarea VLOOKUP cu IF și ISERROR (versiuni anterioare Excel 2007)
Funcția IFERROR a fost introdusă în Excel 2007 pentru Windows și Excel 2016 în Mac.
Dacă utilizați versiunile anterioare, atunci funcția IFERROR nu va funcționa în sistemul dvs.
Puteți replica funcționalitatea funcției IFERROR utilizând combinația dintre funcția IF și funcția ISERROR.
Permiteți-mi să vă arăt rapid cum să utilizați combinația IF și ISERROR în loc de IFERROR.
În exemplul de mai sus, în loc să utilizați IFERROR, puteți utiliza și formula prezentată în celula B3:
= IF (ISERROR (A3), „Not Found”, A3)
Partea ISERROR a formulei verifică erorile (inclusiv eroarea # N / A) și returnează TRUE dacă este găsită o eroare și FALSE dacă nu.
- Dacă este ADEVĂRAT (ceea ce înseamnă că există o eroare), funcția IF returnează valoarea specificată („Nu a fost găsit” în acest caz).
- Dacă este FALS (ceea ce înseamnă că nu există nicio eroare), funcția IF returnează valoarea respectivă (A3 în exemplul de mai sus).
IFERROR Vs IFNA
IFERROR tratează tot felul de erori, în timp ce IFNA tratează numai eroarea # N / A.
Când gestionați erorile cauzate de VLOOKUP, trebuie să vă asigurați că utilizați formula potrivită.
Folosiți IFERROR când vrei să tratezi tot felul de erori. Acum o eroare poate fi cauzată de diverși factori (cum ar fi formula greșită, intervalul denumit greșit ortografiat, nea găsirea valorii de căutare și returnarea valorii de eroare din tabelul de căutare). Nu ar conta pentru IFERROR și ar înlocui toate aceste erori cu valoarea specificată.
Utilizați IFNA atunci când doriți să tratați numai # erori N / A, care sunt mai susceptibile de a fi cauzate de faptul că formula VLOOKUP nu poate găsi valoarea de căutare.
De asemenea, puteți găsi utile următoarele tutoriale Excel:
- Cum se face VLOOKUP Case Sensitive.
- VLOOKUP vs. INDEX / MATCH - Dezbaterea se încheie aici!
- Utilizați VLookup pentru a obține ultimul număr dintr-o listă în Excel.
- Cum se folosește VLOOKUP cu mai multe criterii
- Eroare #NAME în Excel - Ce o cauzează și cum să o remediați!
