Find a value in a table

Two tables, one lookup

On one sheet you have the code MAT. On the other a list: MAT = Maths, CJL = Czech. A lookup brings the name so you do not type it twice.

That is “one truth” in small: the subject list lives in one place. You change Maths to Mathematics in the lookup list — and everywhere the formula reaches, it rewrites itself.

Imagine two sheets.

Ciselnik:

Znamky has in D2 the code MAT. In E2 you want the word Maths. You do not type it. You pull it with a formula.

Newer Sheets / Excel:

=XLOOKUP(A2;Ciselnik!A:A;Ciselnik!B:B)

Three parts: what I am looking for, where it lives, where to take the answer from.

Older Excel:

=SVYHLEDAT(A2;Ciselnik!A:B;2;NEPRAVDA)

English VLOOKUP and FALSE instead of NEPRAVDA. Four parts: what I am looking for, in which table, which column to return, exact match.

NEPRAVDA / FALSE = exact match. Without it it can take a “nearly” closest code. With MAT you do not want “nearly”. You want MAT, or an error.

A mini lookup list

1. Down at the tabs +. A new sheet. Click the name, rename to Ciselnik. No accents, no space. The formula will be easier to type.

2. A1 Kod, B1 Nazev. Bold.
   A2 MAT, B2 Maths.
   A3 CJL, B3 Czech.
   A4 AJ, B4 English.
   WRITE THE CODES THE SAME as you will write them on Znamky. Mat and MAT is often a different key for a lookup.

3. On Znamky in D1 Kod. In D2 write MAT on the Maths row. In D3 CJL. In D4 AJ. On further subjects a code you have in the lookup list — or add a row in the lookup list.

4. In E1 Nazev by formula. In E2, when the code is in D2:

   =XLOOKUP(D2;Ciselnik!A:A;Ciselnik!B:B)

   When the sheet does not know XLOOKUP (#NÁZEV?), use:

   =SVYHLEDAT(D2;Ciselnik!A:B;2;NEPRAVDA)

   or in English =VLOOKUP(D2,Ciselnik!A:B,2,FALSE).

5. Enter. E2 must show Maths. Not the formula. Not #N/A. Drag E down to all the codes. E3 = Czech. E4 = English.

If you already have the name in column A by hand, leave it. The new column E is a test. When it fits, you can pull A with a formula and leave the code in A. One truth is enough in one column.

What you should see after the first Enter

E2 shows Maths. You click E2 — at the top is =XLOOKUP(...) or =SVYHLEDAT(...). D2 is MAT. On Ciselnik in A2 also MAT, in B2 Maths.

If E2 shows #N/A, compare D2 and Ciselnik A2 letter by letter. A space at the end, small mat, Czech MÁT. Lookup is strict. That is what you want.

If E2 shows 0 or empty, it found the code, but the name column is empty. Fill in B2 in the lookup list.

Drag to E3. In the formula D2 changes to D3. The range Ciselnik!A:A (or A:B with SVYHLEDAT) does not move. Here you do not need a dollar — the whole column is already “locked” because you take A:A.

XLOOKUP versus SVYHLEDAT

XLOOKUP is more straightforward. You say: look for this in column A, return from B.

SVYHLEDAT / VLOOKUP can look only in the first column of the table and take columns to the right. Ciselnik!A:B and the number 2 = the second column, so B. If the name was to the left of the code, SVYHLEDAT will not bring it. XLOOKUP will — that is why it is newer.

If school has old Excel, learn SVYHLEDAT. If Sheets, try XLOOKUP. Both do the same job: code → name.

Remember the fourth argument of SVYHLEDAT hard: NEPRAVDA / FALSE. PRAVDA / TRUE is approximate lookup. Leave it for grades and codes.

What often goes wrong

Wrong sheet name. Ciselnik vs Číselník vs ciselnik. The tab and the formula must match. Click in the formula after = and tap with the mouse on column A of the lookup list — the address writes itself.

#REF! You renamed the sheet after writing the formula and something tore off, or you deleted column B. Ctrl+Z, or write the formula again by clicking.

#N/A on every row. You drag, but D3 is empty. An empty code is not in the list. Leave E empty, or fill in the code.

SVYHLEDAT without NEPRAVDA. On very old tables “nearly” hits a different code. Always the fourth argument.

You look up the name, not the code. In D2 is Maths, the lookup list has codes in A. XLOOKUP will not find it. Look for what is in the first column of the list.

Now you 💪

Two checks.

First: XLOOKUP or SVYHLEDAT from D2 to Ciselnik. E2 shows the name. Drag down. No #N/A on codes you have in the list.

Second: in D6 write XYZ. E6 must shout #N/A. That is correct. Add XYZ and some name to the lookup list, or fix D6 to MAT. The error disappears.

Then in the lookup list rewrite one name. The formula on Znamky must move without editing E.

What to take from this lesson

• XLOOKUP / SVYHLEDAT / VLOOKUP looks in a column and returns the value next to it.
• Exact match (NEPRAVDA / FALSE), not “nearly”.
• Lookup list = one truth for names. #N/A is a typo, not the end of the world.

© 2026 Ing. Martin Polak / AlgoRhino · コンテンツ利用規約