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 the 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,” 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
