Formula generică
=VLOOKUP(val&"",table,col,0)
rezumat
Pentru a utiliza funcția VLOOKUP pentru a extrage informații dintr-un tabel în care valorile cheie sunt numere stocate ca text, puteți utiliza o formulă care concatenează un șir gol ("") la valoarea de căutare numerică, constrângându-l la text. În exemplul prezentat, formula din H3 este:
=VLOOKUP(id&"",planets,2,0) // returns "Earth"
unde id (H2) și planetele (B3: B11) sunt denumite intervale.
Notă: Exemplul este o soluție la problema numerelor și a textului nepotrivite, care provoacă o eroare # N / A. Dacă nu există nicio potrivire, soluția nu este necesară și puteți utiliza o formulă VLOOKUP normală.
Explicaţie
O eroare comună VLOOKUP este o nepotrivire între numere și text. Cel mai de obicei, coloana de căutare din tabel conține valori numerice care arată ca numere, dar sunt de fapt numere stocate ca text. Când un număr autentic este trecut în VLOOKUP ca primul argument, formula returnează o eroare # N / A, chiar dacă pare să existe o potrivire. Ecranul de mai jos prezintă un exemplu al acestei probleme:
Numerele din coloana B sunt de fapt text, deci valoarea de căutare numerică, 3, eșuează, chiar dacă se pare că VLOOKUP ar trebui să se potrivească cu B5 și să returneze „Pământ”. Puteți introduce un număr ca valoare text precedând numărul cu un singur ghilimel (').
Cea mai bună soluție este să vă asigurați că valorile de căutare din tabel sunt într-adevăr numere. Cu toate acestea, dacă nu aveți control asupra tabelului, puteți modifica formula VLOOKUP pentru a constrânge valoarea de căutare pentru a se potrivi cu tipul din tabel. În exemplul prezentat, constrângem valoarea de căutare numerică către text concatenând un șir gol:
=VLOOKUP(id,planets,2,0) // original =VLOOKUP(id&"",planets,2,0) // revised
Iar formula revizuită are grijă de eroare:
De asemenea, puteți face același lucru cu o formulă mai lungă care utilizează funcția TEXT pentru a converti numărul în text:
=VLOOKUP(TEXT(id,"@"),planets,2,0)
Atât numerele, cât și textul
Dacă nu puteți fi sigur când veți avea numere și când veți avea text, puteți satisface ambele opțiuni prin împachetarea VLOOKUP în funcția IFERROR și folosind o formulă care tratează ambele cazuri:
=IFERROR(VLOOKUP(id,planets,3,0),VLOOKUP(id&"",planets,3,0))
Aici, încercăm mai întâi o formulă normală VLOOKUP care presupune atât valoarea de căutare, cât și prima coloană din tabele sunt numere. Dacă aceasta generează o eroare, încercăm din nou cu formula revizuită. Dacă și formula respectivă eșuează, VLOOKUP va returna o eroare # N / A ca întotdeauna.
