INDEX and MATCH for flexible lookups

TL;DR:

• MATCH finds the position of a key in a list. INDEX returns the cell at a position. Together they look up in any direction.

• Use 0 as the last MATCH argument for an exact match.

• For a two-way lookup, use two MATCH calls, one for the row and one for the column.

You have a staff table with names in column A and ID codes in column B, and someone hands you a list of IDs. You want the names. The lookup function you know searches the left column and returns something to its right, so it cannot go the other way. INDEX and MATCH can.

Two small functions, each doing one job

MATCH(key, list, 0) answers the question: at which position in this list is the key? If the key is the fourth item, it returns 4. The 0 means exact match, which is what you want for names and codes.

INDEX(list, n) answers the opposite question: what is the item at position n? INDEX of a list and 4 returns the fourth item. Put MATCH inside INDEX and you have a lookup: the position found in one list is used to pick from another list of the same length.

Before you start

• Make sure the two lists have the same number of rows and start on the same row, such as A2:A6 and B2:B6.

• Check the key for stray spaces and for numbers stored as text. A visible E104 with a trailing space will not match E104.

• Decide whether you need an exact match. Almost always you do, so type 0.

Step by step

1. Pick the column you want back: This is the INDEX return range, for example the names in A2:A6.

2. Pick the column you search in: This is the MATCH range, for example the IDs in B2:B6.

3. Write MATCH first: Test =MATCH("E104", B2:B6, 0) alone in a spare cell. You should see a position number.

4. Wrap it in INDEX: Put that MATCH where INDEX wants its position: =INDEX(A2:A6, MATCH("E104", B2:B6, 0)).

5. Replace the typed key with a cell: Use a cell reference such as D2 instead of the text, then fill down for the whole list.

Worked example: looking up a value to the left

The MATCH position of E104 in B2:B6 is 4, because E104 is the fourth ID in that range. Note that it is the position within the range, not the sheet row, which is 5. INDEX then returns the fourth name in A2:A6, which is Dev.

=INDEX(A2:A6, MATCH(D2, B2:B6, 0))

With E104 in D2 this returns Dev. Check by reading the table: the row with E104 shows Dev. Now try E106, which does not exist. MATCH finds nothing and the formula shows an error, which is correct. Do not hide it blindly. An error here means the ID is missing or has a stray space.

Two-way lookups

A grid has row labels and column labels, and the cell you want sits where they cross. INDEX accepts a row number and a column number, and each can come from its own MATCH.

Put the labels in A2:A4 and B1:D1, the numbers in B2:D4, and look up South in February:

=INDEX(B2:D4, MATCH("South", A2:A4, 0), MATCH("Feb", B1:D1, 0))

The first MATCH returns 2, since South is second in A2:A4. The second returns 2, since Feb is second in B1:D1. INDEX takes row 2, column 2 of B2:D4 and returns 95. Reading the grid confirms it: South in Feb is 95.

If you fill this formula down a list of regions and across a row of months, make the region reference lock its column and the month reference lock its row, so each copy still points at its own labels. Then change one number in the grid and watch the looked-up value follow it. That small test proves the formula reads the grid and is not a typed-in answer.

Why people prefer this to a fixed column number

Many lookup formulas carry a column number, such as "return the third column". Insert a column and that number quietly points at the wrong data. With INDEX and MATCH you name the return range itself, so inserting a column inside the grid adjusts the references for you.

Mistakes to avoid

• Leaving off the final 0, which asks for an approximate match and can return a wrong row without any error.

• Using ranges of different sizes or starting rows, so positions no longer line up.

• Forgetting that MATCH returns a position in the range, not a sheet row number.

• Not locking the ranges with dollar signs when you fill the formula down, e.g. $A$2:$A$6.

Doing it faster

If a formula returns an error and you cannot see why, DuckyHelper can look at the sheet on your screen when you press the key and ask, and talk through which part to test first.

Frequently asked questions

Do I need INDEX and MATCH if my app has a newer lookup function?

Not always. Newer functions cover many cases, but INDEX and MATCH work in nearly every spreadsheet app, so they are worth knowing.

Why does my MATCH return an error even though I can see the value?

Usually a trailing space, or a number stored as text on one side. Clean both sides so they are the same type.