INDEX MATCH returnează alt rând în loc de #N/A (lipsește 0 din MATCH)
Fără al treilea argument, MATCH face o căutare aproximativă: când valoarea nu există, întoarce poziția celei mai apropiate valori mai mici, iar INDEX afișează rândul altcuiva, fără nicio eroare. Pune 0 ca ultim argument: =INDEX(B2:B6,MATCH(E1,A2:A6,0)).
Explicat de Tudor Andrei Dumitrescu, Senior Software Developer, fondatorul Academiei ProgramatorilorActualizat la
Ce înseamnă
MATCH(valoare, interval, [match_type]) are trei moduri: 0 = potrivire exactă, 1 (implicit, când lipsește) = cea mai mare valoare mai mică sau egală, -1 = cea mai mică valoare mai mare sau egală. Mulți scriu MATCH(E1,A2:A6) crezând că implicit caută exact.
Cu modul 1, dacă marca 1050 nu există în lista 1010, 1020, 1040, 1060, MATCH nu dă eroare, ci poziția lui 1040, iar INDEX afișează numele angajatului cu marca 1040. Este cea mai periculoasă formă a greșelii, pentru că rezultatul arată corect. Pe o listă nesortată, rezultatul aproximativ poate fi și mai ciudat.
Când o căutare întoarce un rezultat care nu se potrivește cu ce ai căutat, verifică întâi ultimul argument al lui MATCH (sau al lui VLOOKUP).
De ce apare: cauzele frecvente
1Lipsește 0 din MATCH
=INDEX(B2:B6,MATCH(E1,A2:A6))=INDEX(B2:B6,MATCH(E1,A2:A6,0))Fără 0, MATCH alege cea mai apropiată valoare mai mică atunci când nu găsește valoarea exactă. Cu 0 caută doar valoarea identică și dă #N/A dacă nu există.
2#N/A-ul corect nu e tratat
=INDEX(B2:B6,MATCH(E1,A2:A6,0))=IFNA(INDEX(B2:B6,MATCH(E1,A2:A6,0)),"Marcă inexistentă")Odată ce MATCH caută exact, o marcă inexistentă dă #N/A. IFNA îl transformă într-un mesaj clar pentru cine citește raportul.
3Același lucru cu XMATCH sau XLOOKUP
=XLOOKUP(E1,A2:A6,B2:B6,,-1)=XLOOKUP(E1,A2:A6,B2:B6,"Marcă inexistentă")La XLOOKUP potrivirea exactă e implicită. Modul -1 („următorul mai mic”) reproduce aceeași problemă și trebuie folosit doar pentru praguri.
Repară eroarea, direct în pagină
În F2, formula =INDEX(B2:B6,MATCH(F1,A2:A6)) caută numele angajatului cu marca din F1. Marca 1050 nu există, dar F2 afișează numele altui angajat. Repară formula din F2 ca pentru o marcă inexistentă să afișeze textul Marcă inexistentă, iar pentru o marcă existentă numele corect.
Indiciu
MATCH are nevoie de al treilea argument ca să caute exact; apoi tratează #N/A.
Reparația
Caută exact cu 0 și înlocuiește #N/A cu mesajul cerut:
=IFNA(INDEX(B2:B6,MATCH(F1,A2:A6,0)),"Marcă inexistentă")Pentru 1050, F2 afișează Marcă inexistentă; pentru 1040 ar afișa Mihai.
De ce merge
Fără 0, MATCH(1050, …) alege cea mai mare marcă mai mică decât 1050, adică 1040, iar INDEX întoarce Mihai, care nu are legătură cu marca căutată. Cu 0, MATCH caută doar marca identică și, pentru că 1050 lipsește, întoarce #N/A. IFNA transformă acest #N/A în mesajul Marcă inexistentă, iar pentru mărcile care există rezultatul rămâne numele corect.
Alte forme ale mesajului
MATCH returns wrong valueINDEX MATCH returns wrong resultMATCH fără 0POTRIVIRE returnează poziția greșită
Cum o eviți pe viitor
- Scrie mereu
0ca al treilea argument al lui MATCH când cauți un cod, un nume sau o marcă. - Testează căutarea cu o valoare care nu există: trebuie să vezi #N/A sau mesajul tău, nu un rezultat.
- Păstrează potrivirea aproximativă doar pentru tabele de praguri sortate.
- În Excel 365,
XLOOKUPșiXMATCHcaută exact implicit.
Întrebări frecvente
De ce funcționează pentru toate valorile care există?
Pe o listă sortată, căutarea aproximativă găsește și valorile exacte. Problema se vede doar când valoarea lipsește: în loc de #N/A primești rândul vecinului.
E la fel la VLOOKUP?
Da. VLOOKUP fără FALSE are exact același comportament, iar soluția este aceeași: ultimul argument FALSE sau 0.
Erori înrudite
#N/A în MATCH #N/A în MATCH înseamnă că valoarea nu a fost găsită în lookup_array: cauți în coloana greșită, valoarea diferă (spații, text vs număr) sau lipsește 0 pentru potrivire exactă. Într-un INDEX-MATCH eroarea vine aproape mereu din MATCH. VLOOKUP returnează o valoare greșită (lipsește FALSE) Dacă VLOOKUP întoarce prețul altui produs, aproape sigur lipsește al patrulea argument. Fără FALSE, VLOOKUP face căutare aproximativă și ia cea mai apropiată valoare mai mică. Scrie =VLOOKUP(G1,A2:C6,3,FALSE). #N/A în VLOOKUP cu TRUE (valoarea e mai mică decât prima din tabel) Cu TRUE (potrivire aproximativă), VLOOKUP dă #N/A când valoarea căutată este mai mică decât prima valoare din tabelul de praguri. Adaugă în tabel un prag de la 0 sau tratează cazul cu IFNA, de exemplu =IFNA(VLOOKUP(E2,$A$2:$B$4,2,TRUE),0). #N/A în XLOOKUP #N/A în XLOOKUP înseamnă că valoarea căutată nu există în lookup_array. Verifică valoarea (spații, tip număr/text) și, dacă lipsa ei e normală, pune al patrulea argument: =XLOOKUP(G1,A2:A6,B2:B6,"Inexistent"). Î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.
