SVYHLEDAT can only go right. XLOOKUP can do more.
You already know =XLOOKUP(code; code column; name column). Today: what if you look up a name and want a code? When a code in the table is missing? When you need the last match?
The Modeller builds tables that do not fall apart when you add a row or rename something.
Three extra situations
-
Sheet
Ciselnik: A = Code, B = الاسم. OnZnamkyin F2 you want the code according to the name in A2 (Maths):=XLOOKUP(A2;Ciselnik!B:B;Ciselnik!A:A)You look in B, you return from A — left of classic SVYHLEDAT.
-
Code
XYZin D2 does not exist. XLOOKUP with a 4th argument (if_not_found):=XLOOKUP(D2;Ciselnik!A:A;Ciselnik!B:B;"unknown subject")Instead of #N/A a human sentence. A teacher audit will like that.
-
Sheet
Kapesne— column A days. A rate table onCiselnikG1:H2:Mon→50,Tue→30(typical pocket money). B2:=XLOOKUP(A2;Ciselnik!G:G;Ciselnik!H:H;0)Expected minimum by day — you compare with actual B2.
-
Check: change
MathstoMathematicsin the lookup list. E2 (name from code) and F2 (code from name) must fit according to what you change.
Keep the lookup list without spaces in codes. MAT and MAT are two worlds for XLOOKUP. The Sorter with validation, the Modeller with a lookup list.
When SVYHLEDAT
Old Excel without XLOOKUP: SVYHLEDAT looks only to the right. Want a code from a name? Swap columns in the lookup list (A name, B code) or use INDEX+MATCH. In Sheets rather XLOOKUP.
What often goes wrong
Absolute address of the lookup list. When you copy the formula, Ciselnik!A:A is OK. A2:A5 without $ shifts — on an answer range put $A$2:$A$100.
A duplicate code in the lookup list. XLOOKUP takes the first match. One code = one row.
#N/A without handling. In the hand-in explain a missing code — or the 4th argument.
🏫 Grade average — lookup list
Column D subject code, E name from XLOOKUP. Chart still from A:B — names in A or E must be consistent. One change in the lookup list.
💰 Pocket money — expected vs. actual
Column “typical pocket money” from the lookup list by day. Difference column =B2-expected. A model, not just a record.
📊 Class survey — mapping
The form sent Apple — the lookup list maps to apple. XLOOKUP in column C before COUNTIF.
🏆 Mini dashboard — link to the lookup list
The dashboard does not copy the subject list. It shows the average and a MAX code — names from the lookup list via XLOOKUP only when you need a label.
Now you 💪
First: XLOOKUP both ways (code→name, name→code).
Second: a missing code with the text “unknown subject”.
Check: A change in the lookup list shows up in the formulas. #N/A handled. Codes unique.
What to take from this lesson
- XLOOKUP looks left and right, 4th argument = when it finds nothing.
- One lookup list, more sheets — one truth of names.
- Codes without spaces, without duplicates.
© 2026 Ing. Martin Polak / AlgoRhino · شروط استخدام المحتوى