În acest tutorial, vom vedea cum să folosim funcțiile IFERROR și VLOOKUP împreună pentru a prinde și gestiona diferite erori. În plus, veți învăța cum să realizați vlookup-uri secvențiale în Excel, prin aninderea mai multor funcții IFERROR una peste alta.

Excel VLOOKUP și IFERROR – aceste două funcții pot fi destul de greu de înțeles separat, darămite atunci când sunt combinate. În acest articol, veți găsi câteva exemple ușor de urmat care abordează utilizări comune și ilustrează în mod clar logica formulelor.

Dacă nu aveți prea multă experiență cu funcțiile IFERROR și VLOOKUP, ar putea fi o idee bună să revizuiți mai întâi elementele de bază ale acestora urmărind link-urile de mai sus.

• IFERROR cu VLOOKUP pentru a gestiona erorile
  • VLOOKUP și returnează propriul text în loc de eroare N/A
  • VLOOKUP și returnează o celulă goală sau zero dacă nu se găsește nimic
• Nest IFERROR în cadrul VLOOKUP pentru a găsi întotdeauna ceva
• Vlookup-uri secvențiale cu IFERROR-uri imbricate

IFERROR Formula VLOOKUP pentru a gestiona #N/A și alte erori

Când Excel Vlookup nu reușește să găsească o valoare de căutare, acesta aruncă o eroare #N/A, ca aceasta:

În funcție de nevoile dvs. de afaceri, este posibil să doriți să deghizați eroarea cu un text propriu, zero sau o celulă goală.

Exemplu 1. Formula Vlookup Iferror pentru a înlocui toate erorile cu propriul text

Dacă doriți să înlocuiți notația standard de eroare cu textul dvs. personalizat, înfășurați formula VLOOKUP în IFERROR și introduceți orice text doriți în cel de-al doilea argument (value_if_error), de exemplu „Not found”:

Cu valoarea de căutare în B2 în tabelul Main și intervalul de căutare A2:B4 în tabelul Lookup, formula ia următoarea formă:

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

Captura de ecran de mai jos arată formula noastră Excel IFERROR VLOOKUP în acțiune:

Rezultatul pare mult mai ușor de înțeles și mult mai puțin intimidant, nu-i așa?

În mod similar, puteți utiliza INDEX MATCH împreună cu IFERROR:

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

Formula IFERROR INDEX MATCH este deosebit de utilă atunci când doriți să extrageți valori dintr-o coloană care se află la stânga coloanei de căutare (left lookup) și să returnați propriul text atunci când nu se găsește nimic.

Exemplu 2. IFERROR cu VLOOKUP pentru a returna blank sau 0 dacă nu se găsește nimic

Dacă nu doriți să afișați nimic atunci când valoarea de căutare nu este găsită, faceți ca IFERROR să afișeze un șir gol („”):

În exemplul nostru, formula este următoarea:

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

După cum puteți vedea, nu returnează nimic atunci când valoarea de căutare nu se află în lista de căutare.

Dacă doriți să înlocuiți eroarea cu valoarea zero, puneți 0 în ultimul argument:

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

Atenție! Funcția Excel IFERROR prinde toate tipurile de erori, nu numai #N/A. Este bună sau rea? Totul depinde de obiectivul dumneavoastră. Dacă doriți să mascați toate erorile posibile, IFERROR Vlookup este calea cea mai bună. Dar poate fi o tehnică nechibzuită în multe situații.

De exemplu, dacă ați creat un interval denumit pentru datele tabelului dvs. și ați scris greșit acest nume în formula Vlookup, IFERROR va prinde o eroare #NAME? și o va înlocui cu „Not found” sau cu orice alt text pe care îl furnizați. Ca urmare, este posibil să nu vă dați seama niciodată că formula dvs. furnizează rezultate greșite decât dacă observați dvs. înșivă greșeala de scriere. Într-un astfel de caz, o abordare mai rezonabilă ar fi detectarea numai a erorilor #N/A. Pentru aceasta, folosiți formula Vlookup IFNA în Excel pentru Office 365, Excel 209, Excel 2016 și Excel 2013, IF ISNA VLOOKUP în toate versiunile Excel.

Sfârșitul este: fiți foarte atenți atunci când alegeți un companion pentru formula dvs. VLOOKUP 🙂

Încadrați IFERROR în VLOOKUP pentru a găsi întotdeauna ceva

Imaginați-vă următoarea situație: căutați o anumită valoare într-o listă și nu o găsiți. Ce opțiuni aveți? Fie primiți o eroare N/A, fie afișați un mesaj propriu. De fapt, există o a treia opțiune – dacă vlookup-ul dvs. principal se poticnește, atunci căutați altceva care se află cu siguranță acolo!

Să ducem exemplul nostru mai departe, să creăm un fel de tablou de bord pentru utilizatorii noștri care să le arate un număr de extensie al unui anumit birou. Ceva de genul acesta:

Atunci, cum extrageți extensia din coloana B pe baza numărului de birou din D2? Cu această formulă obișnuită de Vlookup:

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

Și va funcționa bine atâta timp cât utilizatorii dvs. introduc un număr valid în D2. Dar ce se întâmplă dacă un utilizator introduce un număr care nu există? În acest caz, lăsați-i să sune la sediul central! Pentru aceasta, încorporați formula de mai sus în argumentul value din IFERROR și puneți un alt Vlookup în argumentul value_if_error.

Formula completă este un pic cam lungă, dar funcționează perfect:

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

Dacă numărul biroului este găsit, utilizatorul primește numărul de extensie corespunzător:

Dacă numărul biroului nu este găsit, se afișează extensia biroului central:

Pentru a face formula un pic mai compactă, puteți utiliza o abordare diferită:

În primul rând, verificați dacă numărul din D2 este prezent în coloana de căutare (vă rugăm să observați că am setat col_index_num la 1 pentru ca formula să caute și să returneze valoarea din coloana A): VLOOKUP(D2,$A$2:$B$7,1,FALSE)

Dacă numărul de birou specificat nu este găsit, atunci căutăm șirul de caractere „birou central”, care se află cu siguranță în lista de căutare. Pentru aceasta, înfășurați primul VLOOKUP în IFERROR și cuibăriți toată această combinație în interiorul unei alte funcții VLOOKUP:

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

Bine, o formulă ușor diferită, același rezultat:

Dar care este motivul pentru a căuta „birou central”, mă puteți întreba. De ce să nu furnizăm numărul de post direct în IFERROR? Pentru că extensia se poate schimba la un moment dat în viitor. Dacă se întâmplă acest lucru, va trebui să actualizați datele doar o singură dată în tabelul sursă, fără să vă faceți griji cu privire la actualizarea fiecăreia dintre formulele VLOOKUP.

Cum să efectuați VLOOKUP-uri secvențiale în Excel

În situațiile în care trebuie să efectuați așa-numitele Vlookup-uri secvențiale sau înlănțuite în Excel, în funcție de faptul că o căutare anterioară a reușit sau a eșuat, cuibăriți două sau mai multe funcții IFERROR pentru a vă executa Vlookup-urile unul câte unul:

Formula funcționează cu următoarea logică:

Dacă primul VLOOKUP nu găsește nimic, primul IFERROR captează o eroare și execută un alt VLOOKUP. Dacă al doilea VLOOKUP nu reușește, al doilea IFERROR captează o eroare și rulează al treilea VLOOKUP, și așa mai departe. Dacă toate Vlookup-urile se poticnesc, ultimul IFERROR returnează mesajul dumneavoastră.

Această formulă IFERROR imbricata este deosebit de utilă atunci când trebuie să faceți Vlookup pe mai multe foi de calcul, așa cum se arată în exemplul de mai jos.

Să spunem că aveți trei liste de date omogene în trei foi de calcul diferite (numere de birou în acest exemplu) și doriți să obțineți o extensie pentru un anumit număr.

Să presupunem că valoarea de căutare se află în celula A2 din foaia curentă, iar intervalul de căutare este A2:B5 în 3 foi de calcul diferite (Nord, Sud și Vest), următoarea formulă funcționează de minune:

=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")))

Atunci, formula noastră de „Vlookups înlănțuite” caută în toate cele trei foi de calcul în ordinea în care le-am imbricate în formulă, și aduce prima potrivire pe care o găsește:

Acesta este modul în care se utilizează IFERROR cu VLOOKUP în Excel. Vă mulțumesc pentru lectură și sper să ne revedem pe blog săptămâna viitoare!

Descărcări disponibile

Excel IFERROR VLOOKUP exemple de formule IFERROR VLOOKUP

Ar putea fi interesat și de

• Excel IFERROR funcție cu exemple de formule
• Utilizarea IF cu VLOOKUP în Excel
• Excel VLOOKUP tutorial pentru începători

.