Excel Lookup Functions Practice: VLOOKUP, XLOOKUP, INDEX MATCH
A lookup finds a value in one place and returns related information from another: a price for a product code, a name for an employee ID, a shipping rate for a zone and weight. It is one of the most useful skills in real spreadsheets, and one of the easiest to get subtly wrong.
The exercises start with VLOOKUP, move to XLOOKUP, which can look to the left, and then to INDEX with MATCH, which also handles two-way tables and finding who has the highest value. Along the way you handle values that are not in the list and bands such as weight ranges. Each exercise is checked on hidden data, so a lookup that only works for the row you can see fails.
What these functions are for
- Return a price, name or rate from a table using a code or ID.
- Show a clear message instead of #N/A when something is missing (IFERROR).
- Read a two-way table: one value picks the row, another picks the column.
- Find who owns the highest, second-highest or lowest value (INDEX, MATCH and MAX).
- Place a number in a band, such as a weight range, with next-smaller matching.
Tips before you start
- Look up an exact match unless you really want a band: ask for exact matching explicitly.
- VLOOKUP counts columns from the first column of the table, and cannot return a column to its left.
- Lock the table with dollar signs before you copy a lookup down.
- Wrap only the lookup you expect to fail in IFERROR, and show a message that says what happened.
Practice exercises, in a sensible order
Look Up a Product's Price
Retrieve a value from a table by searching for a key, so the answer follows the key in the cell instead of a fixed row.
Find an Employee by ID with XLOOKUP
Retrieve values that sit to the left of the lookup column, using XLOOKUP, or INDEX with MATCH, when VLOOKUP cannot.
Handle Missing Prices with IFERROR
Replace an Excel error with a useful result using IFERROR, and understand what IFERROR hides.
Look Up a Shipping Rate with INDEX and MATCH
Combine INDEX with two MATCH functions to read one cell of a table from a row label and a column label.
Who Sold the Most? INDEX, MATCH and MAX
Find the largest, second-largest or smallest value, then use MATCH and INDEX to return the label that belongs to it.
Shipping Rate Bands with INDEX and XMATCH
Look a number up in a table of bands by finding the largest lower limit that does not exceed it.