First, I'll name some ranges to make the formulas easier to read. I'll name the entire table "data," and then use "names" for the list of salespeople. 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.
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 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.
To work this out I'm going to enter the MATCH formulas separately, and then bring 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 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 need to make sure things work properly. Finally, combine the helper functions with the proof-of-concept formula.
The Excel INDEX function returns the value at a given location in a range or array. You can use INDEX to retrieve individual values, or entire rows and columns. The MATCH function is often used together with INDEX to provide row and column...
MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, MATCH is combined...