#N/A în XLOOKUP pe praguri (lipsește match_mode -1 pentru potrivire aproximativă)
XLOOKUP caută implicit potrivirea exactă, deci pe un tabel de praguri (0, 5000, 10000) dă #N/A pentru orice valoare dintre praguri. Adaugă match_mode -1, ca să ia pragul imediat mai mic: =XLOOKUP(E2,$A$2:$A$5,$B$2:$B$5,,-1).
Explicat de Tudor Andrei Dumitrescu, Senior Software Developer, fondatorul Academiei ProgramatorilorActualizat la
Ce înseamnă
La tabelele de tranșe (comisioane, discounturi, tarife pe greutate) nu cauți o valoare exactă, ci tranșa în care cade valoarea. XLOOKUP are însă ca implicit match_mode 0, adică potrivire exactă. Pentru 7200 caută exact 7200 în lista de praguri, nu îl găsește și întoarce #N/A. Doar valorile egale cu un prag (de exemplu 20000) primesc rezultat.
Al cincilea argument al lui XLOOKUP spune cum se potrivește valoarea:
0(implicit): exact;-1: exact sau următoarea valoare mai mică (pragul de jos, ca la VLOOKUP cu TRUE);1: exact sau următoarea valoare mai mare;2: cu wildcard-uri (* și ?).
Al patrulea argument, if_not_found, poate rămâne gol, dar virgula lui trebuie scrisă: =XLOOKUP(E2,A2:A5,B2:B5,,-1). Spre deosebire de VLOOKUP cu TRUE, XLOOKUP cu -1 nu cere ca pragurile să fie sortate.
De ce apare: cauzele frecvente
1Lipsește match_mode
=XLOOKUP(E2,$A$2:$A$5,$B$2:$B$5)=XLOOKUP(E2,$A$2:$A$5,$B$2:$B$5,,-1)Cu -1, 7200 primește tranșa pragului 5000, cel mai mare prag care nu depășește valoarea.
2match_mode pus pe locul lui if_not_found
=XLOOKUP(E2,$A$2:$A$5,$B$2:$B$5,-1)=XLOOKUP(E2,$A$2:$A$5,$B$2:$B$5,,-1)Al patrulea argument e textul afișat când nu se găsește nimic; -1 scris acolo nu schimbă modul de căutare.
3Semnul greșit (1 în loc de -1)
=XLOOKUP(E2,$A$2:$A$5,$B$2:$B$5,,1)=XLOOKUP(E2,$A$2:$A$5,$B$2:$B$5,,-1)Cu 1, XLOOKUP ia pragul următor, mai mare, deci 7200 ar primi comisionul de la 10000.
Repară eroarea, direct în pagină
Comisionul fiecărui agent se ia din tabelul de praguri din A1:B5 (de la 5000 lei se primește 4%, de la 10000 lei 6% etc.). Formulele din F2:F5 dau #N/A pentru toate vânzările care nu sunt exact egale cu un prag. Repară formulele din F2:F5 ca fiecare agent să primească comisionul tranșei lui.
Indiciu
XLOOKUP are un al cincilea argument care îi spune să accepte pragul imediat mai mic.
Reparația
Adaugă match_mode -1 (cu if_not_found gol) în F2 și copiază formula până la F5:
=XLOOKUP(E2,$A$2:$A$5,$B$2:$B$5,,-1)Rezultat: Ana 4%, Mihai 6%, Radu 2%, Ioana 8%.
De ce merge
Fără match_mode, XLOOKUP căuta exact 7200, 12500 și 3000 printre praguri și nu le găsea. Cu -1 ia cel mai mare prag mai mic sau egal cu valoarea: 7200 → 5000 (4%), 12500 → 10000 (6%), 3000 → 0 (2%), iar 20000 rămâne potrivire exactă (8%).
Alte forme ale mesajului
#N/AXLOOKUP approximate matchXLOOKUP match_mode -1XLOOKUP not working with ranges#N/A în XLOOKUP pe tranșe
Cum o eviți pe viitor
- Când cauți într-un tabel de praguri, scrie din start
,,-1la XLOOKUP sauTRUEla VLOOKUP. - Pune pragul de jos 0 în tabel, ca orice valoare pozitivă să aibă o tranșă.
- Testează formula cu o valoare dintre praguri, nu doar cu un prag exact.
Întrebări frecvente
Care e diferența față de VLOOKUP cu TRUE?
VLOOKUP cu TRUE face același lucru, dar cere ca prima coloană să fie sortată crescător, altfel întoarce rezultate greșite. XLOOKUP cu match_mode -1 găsește pragul corect și dacă tabelul nu e sortat.
De ce dă #N/A și pentru valori mai mici decât primul prag?
Pentru că nu există niciun prag mai mic sau egal cu ele. Adaugă în tabel un prag 0 sau folosește if_not_found: =XLOOKUP(E2,A2:A5,B2:B5,0,-1).
Erori înrudite
#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"). #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). 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). IF imbricat dă același rezultat pentru toate valorile mari (condițiile sunt în ordinea greșită) IF se oprește la prima condiție adevărată. Dacă primul test e B2>=50, orice punctaj de 50 sau mai mare (inclusiv 95) primește rezultatul lui și nu mai ajunge la testele pentru 70 sau 90. Scrie pragurile de la cel mai mare la cel mai mic: =IF(B2>=90,"A",IF(B2>=70,"B",IF(B2>=50,"C","F"))). Î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.
