How to use VLOOKUP or INDEX MATCH to find a value

Use a lookup function: give it the value you're looking for, the table to search, and where the answer sits. In Grid by Zaweba, a free offline spreadsheet, VLOOKUP-style lookups search the first column of a table and return a value from a column further across, while INDEX and MATCH together find a position and pick the value at it. Both work the way they do in other spreadsheets.

Lining up the lookup table

A lookup table with one column that identifies each row: a product code, a staff number, a postcode. Every value in that column should be unique, or the lookup will stop at the first one it finds. You also need the value to look up, usually typed into a cell on another sheet or form.

Make the lookup table a proper table if you can. Structured references grow when you add rows, so the formula keeps finding new entries without you editing the range.

VLOOKUP, step by step

VLOOKUP takes four things. First the value to find. Second the table range, where the first column must hold the keys. Third the column number to return, counting the key column as 1. Fourth whether you want an exact match or an approximate one.

For codes, names and anything else that must match precisely, ask for an exact match. Leaving the last argument out gives an approximate match, which assumes the first column is sorted and returns the nearest smaller value. That's right for banded tables such as discounts by quantity, and quietly wrong for nearly everything else.

The column number is VLOOKUP's weak point. Insert a column inside the table and every formula that said 3 now returns the wrong field without any warning. HLOOKUP has the same habit, working across rows instead of down columns.

INDEX and MATCH, step by step

MATCH finds where a value sits in a single row or column and returns its position. Give it the value, the column of keys, and zero for an exact match. INDEX then takes a range and a position and returns what's there. Put MATCH inside INDEX, pointing INDEX at the column you want back, and you have a lookup.

This pair has two real advantages. The return column is a range rather than a number, so inserting columns doesn't break it. And the key column can be anywhere, including to the right of the answer. For a two-way lookup, use one MATCH for the row and another for the column, and INDEX returns the cell where they cross.

In Grid, typing either function brings up autocomplete and a tip showing the current argument in bold, and Insert Function (fx) shows each argument with a live result, which is handy while you're getting a nested formula right.

When a lookup gives the wrong answer

An #N/A error means nothing matched. Look for stray spaces, codes stored as text on one sheet and as numbers on the other, or slightly different spellings. TRIM in a helper column clears extra spaces. IFERROR or IFNA can show a friendlier message when a missing value is expected.

A plausible but wrong answer nearly always means an approximate match was used by accident, or the column number points at the wrong field. Grid's trace precedents arrows show which cells a formula reads, and the coloured reference boxes when you edit it show exactly which ranges it uses.

A newer option worth knowing

Grid also has XLOOKUP and XMATCH, which do the same job with separate lookup and return ranges and a built-in result for when nothing is found. For new workbooks they're usually the simplest choice. VLOOKUP and INDEX MATCH remain the ones you'll meet in sheets other people send, and in Grid by Zaweba they open and calculate just as you'd expect.