IntermediateLesson 13 of 305 min read

Lookups: VLOOKUP, INDEX and MATCH, and XLOOKUP

Find values in a table with VLOOKUP and INDEX with MATCH, handle missing items with IFNA, and use approximate match for bands.

In this lesson you will

  • Use VLOOKUP with exact match
  • Use INDEX and MATCH
  • Handle not found with IFNA
  • Use approximate match for bands

About 15 minutes

Finding a price without scrolling

Imran runs the counter of a stationery shop. He has a price list of four hundred items, and a customer asks for item code N01. Scrolling and squinting is slow and risky. With a lookup he types the code in a cell and Excel returns the price, name and stock in an instant. Lookups are the feature that most often turns a beginner into someone colleagues come to for help, and they are used in invoices, report cards, price lists and almost every business sheet.

VLOOKUP: the classic

=VLOOKUP(what to find, table, column number, FALSE)

VLOOKUP looks for the value in the first column of the table, then returns what sits in the column you number. The last argument FALSE means ‘exact match’, which is what you want almost every time. The sheet used below has a small price list in A1:C5 with codes in A, item names in B and prices in C.

Real results

Formula What it does Result
=VLOOKUP(F1,A2:C5,3,FALSE) Price for code N01 60
=VLOOKUP(F1,A2:C5,2,FALSE) Item name for N01 Notebook
=INDEX(C2:C5,MATCH(F1,A2:A5,0)) Same price, INDEX and MATCH 60
=INDEX(A2:A5,MATCH(“Ruler”,B2:B5,0)) Code for Ruler (looks left) R01
=VLOOKUP(“Z99”,A2:C5,3,FALSE) A code that is not there #N/A
=IFNA(VLOOKUP(“Z99”,A2:C5,3,FALSE),“Not found”) Friendly message Not found

Look at the first two lines. The same code gives a price when the column number is 3 and a name when it is 2. If you insert a column into the table, a hard-coded column number silently points at the wrong thing. This is VLOOKUP’s biggest weakness, and the next function solves it.

INDEX and MATCH: more flexible

MATCH finds the position of a value in a list (the test sheet gave position 3 for Eraser in the item list). INDEX returns the item at a position in another list. Together, =INDEX(C2:C5,MATCH(F1,A2:A5,0)) says ‘find the position of the code, then give the price from the same position’. It can look to the left as well, as the Ruler example shows: it found the code R01 from the name even though the code column sits left of the name. And inserting columns does not break it.

XLOOKUP: the modern way

Excel 2021 and Microsoft 365 (and Google Sheets) have XLOOKUP: =XLOOKUP(F1, A2:A5, C2:C5, “Not found”). It looks in one range, returns from another, can look in any direction and has a built-in message for missing items. The spreadsheet program used to test this lesson could not calculate XLOOKUP, so it is shown as a pattern only and was not run here. If you have it, prefer it; if you share files with older Excel, use INDEX and MATCH.

Handling ‘not found’

Searching for a code that is not in the list returned #N/A. That is the right answer, since the item does not exist, but it looks alarming in a report. IFNA replaces only that error: it produced Not found. Prefer IFNA to IFERROR here, because IFERROR also hides other errors that you should see.

Approximate match for bands

With TRUE (or no last argument) VLOOKUP looks for the largest value that is not bigger than yours, which suits grade bands, tax slabs and discount tiers. The band table must be sorted from smallest to largest: 0 gives F, 50 gives C, 75 gives B and 90 gives A. In the test, a mark of 77 returned B and a mark of exactly 90 returned A. This is neater than long nested IFs, and to change a boundary you edit the table rather than the formulas.

Tidy habits for lookups

  • Keep the lookup table on its own sheet with a clear name.
  • Make codes unique, because lookups return the first match only.
  • Make sure the codes are the same kind of data: a code stored as text will not match one stored as a number.
  • Lock the table range with dollar signs when copying: $A$2:$C$5.
  • Clean spaces with TRIM first.

Practice

Make a three-column price list of at least eight items on a sheet called Prices. On another sheet, build a mini invoice where you type an item code and a quantity and get the name, price and line total. Use VLOOKUP, then rebuild it with INDEX and MATCH. Type a code that does not exist and make the invoice show ‘Not found’ using IFNA.

Practice answer

The line total is quantity times the looked-up price, for example =C2*VLOOKUP(A2,Prices!$A$2:$C$9,3,FALSE). If you get #N/A for a code you can see, check for a space after the code or a number-versus-text mismatch.

Common mistake

Leaving off the last VLOOKUP argument. Without FALSE, Excel assumes an approximate match, and on an unsorted list it can return the wrong item without any warning.

A lookup-friendly price list

Keep the key column first, unique and clean. Sort it if you will use approximate matches. Avoid merged cells, blank rows and titles above the header inside the lookup range. Give the range a name, such as PriceList, by selecting it and typing in the Name Box, so formulas read =VLOOKUP(A2,PriceList,3,FALSE) and do not break when someone adds rows.

Check what you learned

Five quick questions. Pick an answer and you will see straight away whether it is right and why.

What does the FALSE at the end of VLOOKUP mean?

  1. Approximate match
  2. Exact match
  3. Ignore errors
  4. Sort the table

Use it almost every time.

Which pair can look to the left of the lookup column?

  1. VLOOKUP alone
  2. SUM and IF
  3. ROUND and LEN
  4. INDEX and MATCH

VLOOKUP only searches the first column.

What does MATCH return?

  1. A price
  2. A position number
  3. A colour
  4. A sheet name

INDEX uses the position to fetch the value.

Which function replaces only a #N/A error?

  1. ROUND
  2. IFERROR only
  3. COUNT
  4. IFNA

It leaves other errors visible.

When is an approximate match suitable?

  1. Grade bands in a sorted table
  2. Product codes
  3. Phone numbers
  4. Names

Bands need a sorted table.

Summary

  • Lookups find a value in a table by a key.
  • Use exact match (FALSE) unless you want bands.
  • INDEX and MATCH can look left and survive inserted columns.
  • Use IFNA for a friendly not-found message.

Next lesson

Next: text functions for cleaning and combining words.

Questions

Why does my VLOOKUP return #N/A when the code is there?

Look for extra spaces, a text-versus-number mismatch, or a table range that does not start at the lookup column.

Should I learn XLOOKUP or VLOOKUP?

Learn VLOOKUP and INDEX with MATCH because they are everywhere, and use XLOOKUP when your Excel supports it and your readers do too.

Can a lookup return more than one match?

Not directly. It returns the first match. Newer versions have FILTER for returning many; older ones need a PivotTable or helper columns.

Progress is saved in this browser.