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 |
|---|---|---|
| = |
Price for code N01 | 60 |
| = |
Item name for N01 | Notebook |
| = |
Same price, INDEX and MATCH | 60 |
| = |
Code for Ruler (looks left) | R01 |
| = |
A code that is not there | #N/A |
| = |
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, =
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.
The line total is quantity times the looked-up price, for example =
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 =
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?
- Approximate match
- Exact match
- Ignore errors
- Sort the table
Use it almost every time.
Which pair can look to the left of the lookup column?
- VLOOKUP alone
- SUM and IF
- ROUND and LEN
- INDEX and MATCH
VLOOKUP only searches the first column.
What does MATCH return?
- A price
- A position number
- A colour
- A sheet name
INDEX uses the position to fetch the value.
Which function replaces only a #N/A error?
- ROUND
- IFERROR only
- COUNT
- IFNA
It leaves other errors visible.
When is an approximate match suitable?
- Grade bands in a sorted table
- Product codes
- Phone numbers
- 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.
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.
