Acest subiect descrie motivele cele mai comune pentru VLOOKUP pentru un rezultat eronat al funcției și oferă sugestii pentru utilizarea INDEX și MATCH .
Sfat: De asemenea, consultați Sfaturi de depanare pentru cartela de referințe rapide: VLOOKUP , 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 pentru VLOOKUP este că poate căuta doar valori din coloana din partea stângă a matricei de tabel. Dacă valoarea 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.
Rezultatul erorii #N/A, deoarece valoarea de căutare "Kale" apare în a doua coloană (Legume și fructe) a argumentului table_array A2:C10. În acest caz, Excel îl 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. De asemenea, acest lucru poate fi extrem de imposibil 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 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 în schimb
INDEX și MATCH sunt opțiuni bune pentru multe cazuri în care VLOOKUP nu vă îndeplinește cerințele. Avantajul esențial al indexului/MATCH este că puteți căuta o valoare într-o coloană din orice locație din tabelul de căutare. INDEX returnează o valoare dintr-un tabel/interval 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 dintr-un tabel/o 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ă ca coloana de căutare. Acest lucru este diferit de VLOOKUP, în care valoarea returnată trebuie să fie în intervalul specificat. De ce contează acest lucru? Cu VLOOKUP, trebuie să știți numărul coloanei care conține valoarea returnată. Deși acest lucru poate părea dificil, poate fi dificil 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 un rând sau o coloană dintr-o matrice sau le puteți specifica 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, în care puteți căuta doar până la o valoare din prima coloană a unui tabel, INDEX și MATCH vor funcționa dacă valoarea de căutare se află în prima coloană, în ultima sau oriunde în interior.
-
INDEX și MATCH oferă flexibilitatea de a face referire dinamică la coloana care conține valoarea returnată. Acest lucru înseamnă că puteți adăuga coloane la tabel fără a rupe INDEX și MATCH. Pe de altă parte, VLOOKUP se î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 potrivirile. INDEX și MATCH pot găsi o potrivire exactă sau o valoare mai mare sau mai mică decât valoarea de căutare. VLOOKUP va căuta doar o potrivire apropiată cu o valoare (în mod implicit) sau o valoare exactă. De asemenea, VLOOKUP presupune în mod implicit că prima coloană din matricea de tabel este sortată alfabetic și să presupunem că tabelul nu este configurat astfel, VLOOKUP va returna prima potrivire 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 aceasta. Aceasta ia forma:
=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 va match(Kale, care se află undeva în matricea B2:B10, în care valoarea returnată este prima valoare corespunzătoare pentru Kale))
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 și VLOOKUP nu poate găsi o potrivire exactă în datele dvs., returnează eroarea #N/A.
Soluție: Dacă sunteți sigur că datele relevante există î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 neimprimate. 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.
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 ș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.
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 cu potrivirea valorilor, 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 și una dintre coloanele dvs. 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 în virgulă mobilă. Numerele în virgulă mobilă sunt numere care urmează după o virgulă zecimală. (Excel stochează valorile de timp ca numere în virgulă mobilă.) Excel nu poate stoca numere cu puncte flotante foarte mari, astfel încât funcția să funcționeze corect, numerele în virgulă mobilă trebuie rotunjite la 5 zecimale.
Soluție: Scurtați numerele, rotunjindu-le la cinci zecimale cu funcția ROUND.
Aveți nevoie de ajutor suplimentar?
Puteți oricând să întrebați un expert din Comunitatea tehnică Excel sau să obțineți asistență în Comunități.
