Cum să remediați o eroare #N/A în funcția VLOOKUP

Acest subiect descrie motivele cele mai frecvente pentru care VLOOKUP poate să nu reușească.

Sfat: Consultați Cartela de referințe rapide: Sfaturi de depanare VLOOKUP care descrie motivele comune pentru problemele #NA cu VLOOKUP într-un fișier PDF (Portable Document Format) confortabil. Puteți partaja PDF-ul cu alte persoane sau îl puteți imprima pentru referință.

Problemă: Valoarea de căutare nu se află în prima coloană a argumentului matrice_tabel

Una dintre cele mai mari limitări pentru VLOOKUP este că poate căuta doar valorile din coloana cea mai din stânga a matricei de tabel. Așadar, dacă valoarea dvs. de căutare nu se află în prima coloană a matricei, veți vedea eroarea #N/A.

În tabelul următor, dorim să regăsim numărul de unități vândute pentru Kale.

Eroarea #NA în VLOOKUP: Valoarea de căutare nu se află în prima coloană a matricei de tabel

Eroarea apare deoarece valoarea de căutare „Kale” este în a doua coloană (Legume și fructe) din argumentul matrice_tabel A2:C10, astfel că Excel o caută în coloana A, nu în coloana B.

Soluție: Puteți încerca să remediați problema ajustând VLOOKUP astfel încât să facă referire la coloana corectă. Dacă acest lucru nu este posibil, încercați să mutați coloanele. Acest lucru poate fi foarte nepractic dacă aveți foi de calcul mari sau complexe, în care valorile celulelor sunt rezultate ale altor calcule, sau poate că există alte motive logice pentru care chiar nu puteți muta coloanele. Soluția este să utilizați o combinație a funcțiilor INDEX și MATCH, care poate căuta o valoare dintr-o coloană indiferent de poziția sa în tabelul de căutare.

Utilizați INDEX/MATCH în locul funcției VLOOKUP

Combinația INDEX/MATCH poate fi utilizată atunci când VLOOKUP nu îndeplinește necesitățile dvs. Cel mai mare avantaj al INDEX/MATCH este că puteți căuta o valoare dintr-o coloană în orice locație din tabelul de căutare. INDEX returnează o valoare dintr-un tabel sau dintr-o zonă specificată pe baza poziției sale, iar MATCH returnează poziția relativă a unei valori într-un tabel sau o zonă. Utilizând funcțiile INDEX și MATCH împreună într-o formulă, puteți căuta o valoare dintr-un tabel sau o matrice, specificând poziția relativă a valorii în matrice sau tabel.

Există mai multe avantaje ale utilizării INDEX/MATCH în loc de VLOOKUP:

  • Cu INDEX și MATCH, valoarea returnată nu trebuie să fie în aceeași coloană ca și coloana de căutare, spre deosebire de VLOOKUP, unde valoarea returnată trebuie să fie în zona specificată. De ce contează acest lucru? Cu VLOOKUP, trebuie să știți numărul coloanei care conține valoarea returnată. Deși nu pare să fie important, poate fi dificil atunci când aveți un tabel mare și trebuie să contorizați numărul de coloane. De asemenea, dacă ar trebui să adăugați sau să eliminați o coloană din tabel, ar trebui să renumărați și să actualizați argumentul num_index_col. Cu INDEX și MATCH, nu trebuie să numărați nimic, deoarece coloana de căutare este diferită de coloana cu valoarea returnată.

  • Cu INDEX și MATCH, puteți specifica un rând sau o coloană dintr-o matrice sau chiar pe ambele. Acest lucru înseamnă că puteți căuta valori atât vertical, cât și orizontal.

  • INDEX și MATCH pot fi utilizate pentru a căuta valori în orice coloană. Spre deosebire de VLOOKUP, cu care puteți căuta doar o valoare din prima coloană a unui tabel, INDEX și MATCH vor funcționa dacă valoarea dvs. de căutare se află în prima coloană, în ultima sau în oricare alta.

  • INDEX și MATCH oferă flexibilitatea de a face referiri dinamice la coloana care conține valoarea returnată. Acest lucru înseamnă că puteți adăuga coloane la tabel, iar INDEX și MATCH nu vor returna eroare. Pe de altă parte, VLOOKUP returnează eroare dacă ați adăugat o coloană în tabel, deoarece folosește o referință statică la tabel.

  • INDEX și MATCH oferă mai multă flexibilitate cu ajutorul potrivirilor. INDEX și MATCH pot găsi o potrivire exactă, o valoare mai mare sau una mai mică decât valoarea de căutare. VLOOKUP va căuta numai cea mai bună potrivire la o valoare (în mod implicit) sau o valoare exactă. În plus, VLOOKUP presupune implicit că prima coloană a matricei de tabel este sortată în ordine alfabetică, iar dacă tabelul dvs. nu este configurat astfel, VLOOKUP va returna prima potrivire apropiată din tabel, care poate să nu fie cea dorită.

Sintaxă

Pentru a construi o sintaxă pentru INDEX/MATCH, trebuie să utilizați argumentul matrice/referință din funcția INDEX și să imbricați sintaxa MATCH în acesta. Va arăta aproximativ astfel:

=INDEX(matrice sau referință, MATCH(valoare_căutare,matrice_căutare,[tip_potrivire])

Să utilizăm INDEX/MATCH pentru a înlocui VLOOKUP în exemplul de mai sus. Sintaxa va arăta astfel:

=INDEX(C2:C10,MATCH(B13,B2:B10,0))

Mai simplu, înseamnă:

=INDEX(Doresc valoarea returnată din C2:C10, care va potrivi MATCH(Kale, care se află undeva în matricea B2:B10, unde valoarea returnată este prima valoare corespunzătoare pentru Kale))

Funcțiile INDEX și MATCH pot fi utilizate ca înlocuitori pentru VLOOKUP

Formula caută prima valoare din C2:C10 care corespunde la Kale (din B7) și returnează valoarea din C7 (100), adică prima valoare care se potrivește cu Kale.

Problemă: Nu se găsește potrivirea exactă

Atunci când argumentul căutare_zonă este FALSE, iar VLOOKUP nu poate găsi o potrivire exactă în datele dvs., returnează eroarea #N/A.

Soluție: Dacă sunteți sigur că datele relevante se află în foaia de calcul și VLOOKUP nu le găsește, verificați dacă celulele la care faceți referire nu au spații ascunse sau caractere neimprimabile. De asemenea, asigurați-vă că celulele au tipul de date corect. De exemplu, celulele cu numere ar trebui să fie formatate ca Număr, nu Text.

Utilizați funcțiile CLEAN sau TRIM pentru a curăța datele din celule.

Problemă: Valoarea de căutare este mai mică decât cea mai mică valoare din matrice

Dacă argumentul căutare_zonă este setat la TRUE și valoarea de căutare este mai mică decât cea mai mică valoare din matrice, veți vedea eroarea #N/A. TRUE caută o potrivire aproximativă în matrice și returnează cea mai apropiată valoare mai mică decât valoarea de căutare.

În exemplul următor, valoarea de căutare este 100, dar nu există valori în zona B2:C10 mai mici decât 100; de aici apare eroarea.

Eroarea N/A din VLOOKUP atunci când valoarea de căutare este mai mică decât cea mai mică valoare din matrice

Soluție:

  • Corectați valoarea de căutare după cum este necesar.

  • Dacă nu puteți modifica valoarea de căutare și aveți nevoie de mai multă flexibilitate la potrivirea valorilor, luați în considerare utilizarea INDEX/MATCH în locul funcției VLOOKUP. Cu INDEX/MATCH, puteți căuta valori mai mari decât, mai mici decât sau egale cu valoarea de căutare. Pentru mai multe informații despre utilizarea combinației INDEX/MATCH în locul funcției VLOOKUP, consultați secțiunea anterioară din acest subiect.

Problemă: Coloana de căutare nu este sortată în ordine ascendentă

Dacă argumentul căutare_zonă este setat la TRUE și una dintre coloanele de căutare nu este sortată în ordine ascendentă (A-Z), veți vedea eroarea #N/A.

Soluție:

  • Modificați funcția VLOOKUP pentru a căuta o potrivire exactă. Pentru a aceasta, setați argumentul căutare_zonă la FALSE. Nu este necesară sortarea pentru FALSE.

  • Utilizați funcția INDEX/MATCH pentru a căuta o valoare într-un tabel nesortat.

Problemă: Valoarea este un număr mare în virgulă mobilă

Dacă aveți valori de timp sau numere zecimale mari în celule, Excel returnează eroarea #N/A din cauza preciziei virgulei mobile. Numerele în virgulă mobilă sunt numerele care urmează după o virgulă zecimală. (Rețineți că Excel stochează valorile de timp ca numere în virgulă mobilă.) Excel nu poate stoca numere cu virgulă mobilă mari, astfel că pentru ca funcția să funcționeze corect, numerele în virgulă mobilă vor trebui rotunjite la 5 zecimale.

Soluție: Scurtați numerele, rotunjindu-le la cinci zecimale cu funcția ROUND.

Aveți o întrebare cu privire la funcții?

Postați o întrebare în forumul Comunității Excel

Ajutați-ne să îmbunătățim Excel

Aveți sugestii despre cum putem îmbunătăți următoarea versiune de Excel? În acest caz, consultați subiectele de la UserVoice pentru Excel.

Consultați și

Corectarea unei erori #N/A

VLOOKUP: Fără #NA

HLOOKUP, VLOOKUP, LOOKUP returnează valori incorecte în Excel

Calculele cu virgule mobile pot returna rezultate incorecte în Excel

Cartelă de referințe rapide: Recapitulare VLOOKUP

Funcția VLOOKUP

Prezentarea generală a formulelor din Excel

Cum să evitați formulele eronate

Utilizarea verificării erorilor pentru detectarea erorilor din formule

Toate funcțiile Excel (în ordine alfabetică)

Toate funcțiile Excel (după categorie)

Extindeți-vă competențele
Explorați instruirea
Fiți primul care obține noile caracteristici
Alăturați-vă utilizatorilor Office Insider

Au fost utile aceste informații?

Vă mulțumim pentru feedback!

Vă mulțumim pentru feedback! Se pare că ar fi util să luați legătura cu unul dintre agenții noștri de asistență Office.

×