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

Acest subiect descrie cele mai comune motive VLOOKUP pentru un rezultat eronat al funcției și oferă în schimb sugestii pentru utilizarea indexului și a potrivirilor .

Sfat: De asemenea, consultați fișa de referințe rapide: VLOOKUP sfaturi de depanare care prezintă motivele comune pentru problemele #NA într-un fișier PDF convenabil. 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

O restricție de VLOOKUP este că poate căuta doar valori în coloana din stânga, din matricea de tabele. Dacă valoarea de căutare nu se află în prima coloană a matricei, veți vedea eroarea #N.

Î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

Rezultatele erorii #N, deoarece valoarea de căutare "Kale" apare în a doua coloană (produse) a argumentului table_array a2: C10. În acest caz, 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. Aceasta poate fi, de asemenea, foarte imposibilă, dacă aveți foi de calcul mari sau complexe în care valorile celulelor sunt rezultatele altor calcule sau poate există alte motive logice pentru care pur și simplu 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. Consultați secțiunea următoare.

Luați în considerare utilizarea INDEX/MATCH

Index și Match sunt opțiuni bune pentru multe cazuri în care VLOOKUP nu îndeplinește cerințele dvs. Avantajul cheie al indicelui/MATCH este faptul că puteți căuta o valoare într-o coloană în orice locație din tabelul de căutare. INDEX returnează o valoare dintr-un tabel/zonă specificat, în funcție de poziția sa. MATCH returnează poziția relativă a unei valori dintr-un tabel/zonă. Utilizați INDEX și MATCH împreună într-o formulă pentru a căuta o valoare într-un tabel/matrice, specificând poziția relativă a valorii din tabel/matrice.

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ă cu coloana de căutare. Acest lucru este diferit de VLOOKUP, în care 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 este posibil să nu vi se pară o provocare, poate fi greoaie atunci când aveți un tabel mare și trebuie să contorizați numărul de coloane. De asemenea, dacă adăugați/eliminați o coloană din tabel, trebuie să renumărați și să actualizați argumentul col_index_num . 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 fie un rând, fie o coloană dintr-o matrice sau specificați ambele. Acest lucru înseamnă că puteți căuta valori atât vertical, cât și orizontal.

  • INDEXul și meciul pot fi utilizate pentru a căuta valori în orice coloană. Spre deosebire de VLOOKUP, în care puteți căuta doar în sus o valoare din prima coloană dintr-un tabel, INDEX și MATCH vor funcționa dacă valoarea de căutare se află în prima coloană, Ultima sau oriunde în mijloc.

  • INDEX și MATCH oferă flexibilitatea de a face referințe dinamice la coloana care conține valoarea returnată.Acest lucru înseamnă că puteți adăuga coloane la tabel fără a sparge INDEXul și a se potrivi. Pe de altă parte, VLOOKUP întrerupe dacă trebuie să adăugați o coloană la tabel, deoarece face o referință statică la tabel.

  • INDEX și MATCH oferă mai multă flexibilitate cu meciuri.INDEX și MATCH pot găsi o corespondență exactă sau o valoare mai mare sau mai mică decât valoarea de căutare. VLOOKUP va căuta doar un chibrit pentru o valoare mai apropiată (în mod implicit) sau o valoare exactă. VLOOKUP presupune, de asemenea, în mod implicit că prima coloană din matricea de tabel este sortată în ordine alfabetică și să presupunem că tabelul nu este configurat în acest fel, VLOOKUP va returna prima corespondență cea mai apropiată din tabel, care poate să nu fie datele pe care le căutați.

Sintaxă

Pentru a construi sintaxa pentru INDEX/MATCH, trebuie să utilizați argumentul matrice/referință din funcția INDEX și să imbricați sintaxa MATCH în interiorul acestuia. Aceasta preia formularul:

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

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

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

Mai simplu, înseamnă:

= INDEX (returnează o valoare din C2: C10, care se va potrivi (Kale, care se află undeva în matricea B2: B10, în care valoarea returnată este prima valoare care corespunde cu 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 range_lookup este false-iar VLOOKUP nu poate găsi o corespondență exactă în datele dvs., returnează eroarea #N.

Soluție: dacă sunteți sigur că datele relevante există în foaia de calcul și VLOOKUP nu este prins, luați timp pentru a verifica dacă celulele la care se face referire nu au spații ascunse sau caractere neimprimabile. De asemenea, asigurați-vă că celulele urmează tipul corect de date. De exemplu, celulele cu numere ar trebui să fie formatate ca numărși nu text.

De asemenea, luați în considerare utilizarea funcției 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 range_lookup este setat la True-iar valoarea de căutare este mai mică decât cea mai mică valoare din matrice, veți vedea eroarea #N. 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 o flexibilitate mai mare cu valori care se potrivesc, luați în considerare utilizarea INDEX/MATCH în loc de VLOOKUP — consultați secțiunea de mai sus din acest articol. 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 range_lookup este setat la True-iar una dintre coloanele de căutare nu este sortată în ordinea ascendentă (a-Z), veți vedea eroarea #N.

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 de zecimale mari în celule, Excel returnează eroarea #N, din cauza preciziei de punct plutitor. Numerele de puncte flotante sunt numere care urmează după un punct zecimal. (Excel stochează valori de timp ca numere de puncte flotante.) Excel nu poate stoca numere cu puncte plutitoare foarte mari, așadar, pentru ca funcția să funcționeze corect, numerele de puncte flotante 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

Notă:  Această pagină a fost tradusă automatizat și poate conține erori gramaticale sau inexactități. Scopul nostru este ca acest conținut să vă fie util. Ne puteți spune dacă informațiile au fost utile? Aici se află articolul în limba engleză, ca referință.

Extindeți-vă competențele Office
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.

×