#REF! în VLOOKUP
#REF! în VLOOKUP înseamnă că al treilea argument, col_index_num, cere o coloană care nu există în interval: de exemplu 4 pentru un interval de 3 coloane. Micșorează numărul sau lărgește intervalul.
Explicat de Tudor Andrei Dumitrescu, Senior Software Developer, fondatorul Academiei ProgramatorilorActualizat la
Ce înseamnă
Al treilea argument al lui VLOOKUP este numărul coloanei din interval din care se ia rezultatul: 1 este prima coloană a intervalului (cea în care se caută), 2 a doua și așa mai departe. Numărul nu este litera coloanei din foaie și nu se numără de la A, ci de la începutul intervalului.
Dacă intervalul A2:C6 are 3 coloane și ceri coloana 4, Excel nu are de unde să ia valoarea și întoarce #REF!, o referință invalidă. Se întâmplă des după ce ștergi o coloană din tabel (intervalul se micșorează, dar numărul din formulă rămâne) sau când copiezi formula de la un tabel mai lat.
La fel apare #REF! în INDEX cu un număr de rând sau de coloană mai mare decât intervalul.
De ce apare: cauzele frecvente
1Numărul coloanei e mai mare decât intervalul
=VLOOKUP(G1,A2:C6,4,FALSE)=VLOOKUP(G1,A2:C6,3,FALSE)A2:C6 are trei coloane (A, B, C). Prețul este a treia coloană a intervalului, deci numărul corect este 3.
2Intervalul nu cuprinde coloana rezultatului
=VLOOKUP(G1,A2:B6,3,FALSE)=VLOOKUP(G1,A2:C6,3,FALSE)Intervalul trebuie să se întindă până la coloana din care vrei rezultatul.
3Ai numărat coloanele de la A, nu de la începutul intervalului
=VLOOKUP(G1,B2:C6,3,FALSE)=VLOOKUP(G1,B2:C6,2,FALSE)Intervalul începe în B, deci prețul din C este a doua coloană a intervalului.
Repară eroarea, direct în pagină
În G2, formula =VLOOKUP(G1,A2:C6,4,FALSE) trebuie să afișeze prețul produsului cu codul din G1 (P04), dar dă #REF!. Repară formula din G2 ca să afișeze prețul.
Indiciu
Numără câte coloane are intervalul A2:C6 și pe a câta dintre ele se află prețul.
Reparația
Intervalul A2:C6 are 3 coloane, iar prețul este în a treia:
=VLOOKUP(G1,A2:C6,3,FALSE)Rezultatul din G2 este 24.
De ce merge
col_index_num numără coloanele intervalului, începând cu 1 la coloana de căutare. În A2:C6 coloanele sunt: 1 = Cod, 2 = Produs, 3 = Preț. Cu 4, VLOOKUP cere o coloană care nu există în interval și întoarce #REF!. Cu 3 ia prețul de pe rândul găsit. Varianta fără număr, care nu se strică la inserarea coloanelor, este =XLOOKUP(G1,A2:A6,C2:C6).
Alte forme ale mesajului
#REF!#REF! în INDEX
Cum o eviți pe viitor
- Numără coloanele de la prima coloană a intervalului, nu de la A.
- După ce ștergi sau inserezi coloane, verifică formulele VLOOKUP care folosesc acel tabel.
- Folosește
XLOOKUP: îi dai direct coloana rezultatului, fără număr, iar inserarea de coloane nu-l strică. - Sau calculează numărul cu
MATCH("Preț",A1:C1,0)în loc să-l scrii de mână.
Întrebări frecvente
De ce după ce am șters o coloană VLOOKUP dă #REF!?
Intervalul s-a micșorat cu o coloană, dar numărul coloanei din formulă a rămas același, deci acum arată în afara intervalului. Scade numărul sau, mai bine, trece la XLOOKUP.
De ce după ce am inserat o coloană VLOOKUP dă alt rezultat, fără eroare?
Intervalul s-a lărgit, dar numărul coloanei a rămas, așa că acum indică altă coloană. E aceeași fragilitate: numărul coloanei e scris de mână.
Erori înrudite
#N/A în VLOOKUP #N/A în VLOOKUP înseamnă că valoarea căutată nu a fost găsită în prima coloană a tabelului. De obicei e un spațiu în plus, o greșeală de scriere sau codul chiar lipsește: curăță valoarea cu TRIM și pune FALSE ca ultim argument. #N/A în VLOOKUP: valoarea căutată nu e în prima coloană VLOOKUP caută numai în prima coloană a intervalului și întoarce doar coloane din dreapta ei. Dacă valoarea căutată e în a doua coloană sau vrei o coloană din stânga, primești #N/A. Folosește XLOOKUP sau INDEX cu MATCH. #REF! după ștergerea unui rând sau a unei coloane #REF! înseamnă că formula trimite la o celulă care nu mai există, de obicei pentru că ai șters rândul, coloana sau foaia la care se referea. Rescrie referința (de preferat cu un interval, =SUM(B2:B6)) sau dă Undo imediat după ștergere. Învață Excel de la zero, cu exerciții la fiecare lecție
Curs Excel: lecții scurte, fiecare cu un exercițiu verificat automat, direct în browser. Primele 20 lecții sunt gratuite.
