In this video, we’ll look at how to set up the classic two-way lookup using INDEX and Match.
Here we have a list of salespeople with monthly sales figures.
What we want to do is add a formula in Q6 that looks up and retrieves a sales number based on name and month above.
To do this, we’ll use the INDEX and MATCH functions.
First, I’ll name some ranges to make the formulas easier to read. I’ll name the entire table “data”, then use “names” for the list of salespeople. Notice that I’m including the first empty cell in both names. That’s because easier to use the same origin for both the data and labels.
Finally, I’ll name the months. Again, I’ll include the first cell. Now we have 3 ranges.
Next let’s build a proof-of-concept formula that uses INDEX to retrieve a value based on hard-coded row and column numbers. The array is data, and I’ll use 2 for both the row and column number.
INDEX returns 11,882, which is at the intersection of the second row and 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.
To work this out, I’m going to enter the MATCH formulas separately, then put them together with INDEX at the end. 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 type is zero because we want only exact matches.
To match month, we need Q5 for match value, and “months” for lookup array. Match type is again zero.
With Dove and Jan, we get row 8 and column 2. And if we check the table, this is correct.
To wrap things up, I just need to replace the hard-coded values in the INDEX formula with the MATCH functions we created. The easiest way to do that is just to copy the formulas and paste them back into the INDEX function at the right place.
The name match formula goes in for the row number, and the month match formula goes in for the column.
Now the formula is complete, and will lookup the right sales number using both name and month.
When you working out a more complex formula for the first time, this is a good approach. Build your proof-of-concept formula first, then build the helper formulas you need and make sure things work properly. Finally, combine the helper functions with the proof-of-concept formula.