Funcognito — Explore and learn anything, you got this!

2.3 Lookups: XLOOKUP, INDEX/MATCH and VLOOKUP

Checked against Microsoft's Excel for Windows keyboard references, August 2026

తెలుగులో చదవండి

What you'll be able to do

Join two lists that share a key: fees against admission numbers, marks against roll numbers. When some rows come back as #N/A, say which of the three possible reasons caused each miss.

Before you start

Download the lookup-and-join workbook. It holds two sheets that need joining, with traps planted in the keys on purpose.

The work

  1. Same either way: learn XLOOKUP first. =XLOOKUP(A2, Students!A:A, Students!C:C) says: find this value, in that column, and return the matching row from this other column. Write it for the workbook's first join and fill it down with CtrlD.
  2. Same either way: give every lookup its not-found answer up front. =XLOOKUP(A2, Students!A:A, Students!C:C, "not found") does that. Without the fourth part, a miss shows as #N/A. With it, the sheet says in words what happened, and a reader does not mistake a miss for a mistake.
  3. Same either way: XLOOKUP matches exactly unless you tell it otherwise, which is what joining on a key must do. The older functions matched roughly by default. On unsorted data that returned wrong rows that looked believable, and a generation of broken sheets came from it. When you do want a rough match, for tax slabs or grade bands, ask for it on purpose with the match-mode part.
  4. Same either way: learn to read INDEX and MATCH, because older sheets are full of them. =INDEX(Students!C:C, MATCH(A2, Students!A:A, 0)) works in two moves. MATCH finds the row number, and INDEX returns that row from the result column. It is XLOOKUP built from parts, and it was the professional's choice before XLOOKUP existed.
  5. Same either way: VLOOKUP is the one you will inherit most often. =VLOOKUP(A2, Students!A:E, 3, FALSE) needs the key in the first column of the range. The result is chosen by counting columns, and the FALSE that demands an exact match must be typed every time. Counted columns break when somebody inserts a column. Read it fluently, and write XLOOKUP.
  6. Same either way: now the misses. #N/A has exactly three causes, and the workbook plants all three. Test in this order. First, extra spaces: compare =LEN(A2) with the length you can see, and retry using =TRIM(A2) as the key. Second, text against number. A key stored as text will not match the same digits stored as a number. The position in the cell gives it away, because text sits on the left. Third, real absence: the key is genuinely not in the other list, which is a fact about the data and not an error to fix.
  7. Same either way: check one row's answer by hand. On the other sheet, press CtrlF, search for the key, and compare what you find with what the formula returned. One manual check for each join is cheap insurance.

Try it

Join the workbook's two sheets with XLOOKUP, including the not-found message. Three rows come back missing. Work out the exact cause of each one: a space, a text-number, and one truly absent. Write the cause beside each row.

Check yourself

=XLOOKUP(A2, Fees!A:A, Fees!B:B, "not found") — the key, the column to search, the column to return, and the answer for a miss.

On unsorted data it returns a believable wrong row instead of an error. The sheet looks fine and is lying. An exact match fails loudly, which is what a join should do.

Extra spaces: compare =LEN() on both and retry with =TRIM(). Text stored where a number is expected: the same digits, stored differently, and text sits on the left of the cell.

When the key genuinely is not in the other list. The lookup did its job, and the miss is information about the data.

Inserting a column inside the range. VLOOKUP counts columns by number, so every count shifts. The other two name their columns directly.

Go deeper

We haven't checked most of these for screen reader use yet.

Back to Lookups: XLOOKUP, INDEX/MATCH and VLOOKUP: work through the checklist