How to do a two-way lookup with INDEX and MATCH

Here we have a list of salespeople with monthly sales figures.

To do this, we’ll use theINDEXandMATCHfunctions.

First, I’llname some rangesto make the formulas easier to read.

Notice that I’m including the first empty cell in both names.

That’s because it’s easier to use the same origin for both the data and the labels.

Finally, I’ll name the months; again, I’ll include the first cell.

Now we have three ranges.

INDEX returns 11,882, which is at the intersection of the second row and the second column.

Technically, INDEX returns a reference to cell C5, but that’s a topic for another day.

So now we know that INDEX will do the job.

We just need to figure out how to use MATCH to get the right row and column numbers.

First, I’ll enter a name and month, so we have something to match against.

To match Name, we need Q4 for the match value and “names” for the lookup array.

Match throw in is zero because we want only exact matches.

To match Month, we need Q5 for match value and “months” for lookup array.

Match key in is again zero.

With “Dove” and “Jan,” we get row 8 and column 2.

And if we check the table, this is correct.

Now the formula is complete and will look up the right sales number using both name and month.

When you’re working out a more complex formula for the first time, this is a good approach.

Build a proof-of-concept formula first, and then build the helper functions you gotta ensure things work properly.

Finally, combine the helper functions with the proof-of-concept formula.