VLOOKUP and INDEX/MATCH practice

VLOOKUP remains common in existing business workbooks. INDEX/MATCH is another useful pattern, especially when the return column is to the left of the identifier. Learning both makes it easier to read a workbook someone else built.

VLOOKUP’s table rule

VLOOKUP searches the leftmost column of the table you give it. If A2:E7 contains SKUs, item names, stock, reorder levels, and prices, the price is column 5 within that table:

=VLOOKUP("MS-02",A2:E7,5,FALSE)

FALSE requests an exact match. Leaving out that final argument uses approximate-match behavior, which depends on appropriately sorted data. For identifiers such as SKUs, an explicit exact match is usually the clearer choice.

Count from the table’s left edge

The return column number belongs to the selected table, not the worksheet as a whole. If a table starts at column C, its first column is still number 1. Changing the table boundary without changing the return index can return the wrong metric.

Build INDEX/MATCH in two pieces

First, find the identifier’s position inside the lookup range. Then ask INDEX to return that position from the price range:

=MATCH("DK-06",A2:A7,0)
=INDEX(E2:E7,MATCH("DK-06",A2:A7,0))

The zero in MATCH requests an exact match. MATCH returns a relative position, not an absolute worksheet row. If it returns 6, that means the sixth item in the selected range.

Diagnose the result

#N/A often means the lookup value was not found. Check extra spaces, identifiers stored differently, and the selected range. #REF! can mean the requested return column is outside the table. A plausible wrong value can mean the row alignment is off, which deserves attention even when no error appears.

Choose readable formulas

For new workbooks on a compatible version, XLOOKUP can make a simple relationship easier to read. For older files, understanding VLOOKUP and INDEX/MATCH helps you repair the existing logic without guessing. The Lookup Library gives you separate quests for each building block.

Continue your adventure

Use the formula spellbook for syntax or explore the full quest map. You can practice in the browser and download workbooks without creating an account.