The Perfect Match

Heading back into the mailbag, Alison sends us this problem: I have some students with the same last name and the index/match doesn't seem to be doing what I need it to.  It's only finding the first name in the list with that last name.  So I can pull the report for Y. Gordon in 1st period, but not T. Gordon down in 4th period.

I pointed her to Debra Dalgleish's post from July that does a lovely job of showing how to check multiple criteria with INDEX and MATCH, but Alison still felt a bit overwhelmed. I knew what she meant. Sometimes, it's hard to translate an example that doesn't look like yours into the problem you're trying to solve---even when the formulas are the same. So let me share a classroom example.

We need some music to set the stage for our Match Game, don't you think?

Here is our class list. We have a Charlie and Sally Brown in the same class. Ditto for Linus and Lucy Van Pelt. If we want to build a report or find scores, we can't just use the last name to find a student. This isn't unusual for any given class--and while you might end up with a class list of unique last names, you are more likely to end up with 2 or more kids with the same first name.

The easy way out of this problem when building reports is to use the student number or another unique identifier for each student. Then, just use your regular INDEX/MATCH coupling to drive the report. But we're not taking the low road today, my friends.

Suppose we're building a report and need to find the score for each student at the end of the quarter (Column J). For the purposes of this example, we'll use cell C16 on the Report page (Report!C16) as our reference for a cell that already has the last name of a student.

Ordinarily, we might build a formula like this: =INDEX(J8:J17, MATCH(Report!C16,A8:A17,0))

We tell Excel to INDEX the range of scores for second quarter (J8:J17) and MATCH our reference cell (Report!C16) with the list of student last names (A8:A17). Alas, this isn't going to work if we need to find scores for Sally Brown or Lucy Van Pelt. Excel is going to stop when it runs across the first instance of the last name "Brown" or "Van Pelt."

We're going to need an array formula to do some heavy lifting here. Remember, these are formulas where you have to use CTRL+SHFT+ENTER (not just ENTER) to get them to work. This formula is structured very similarly to the plain Jane version.

To keep things simple for this demo, let's say that the formula is going in cell J19 for Sally Brown. (Yes, I know you would just look it up if you were on the same page. Work with me here.) Here is our new and improved INDEX/MATCH formula:

=INDEX(J8:J17,MATCH(1,(A19=A8:A17)*(B19=B8:B17),0))

We're still INDEX'ing the same column (J8:J17), but we're going to shake up the MATCH part of the equation to connect with two variables. We set up one set of matchmaking between cell A19 (Brown) and the range with the last names (A8:A17) and another between cell B19 (Sally) and the range with first names. In between, we put a "*" to direct Excel to combine these values.

Want to play with it? Have it in the embedded worksheet below. Change the names and watch the magic happen.

You can also click the Excel icon at the bottom of the window to download the workbook.



Bonus Round
Have an Excel question you want to air out here?  Drop me a line. I do have a couple more unanswered comments from previous posts to respond to...so if you've been waiting for an answer, stay tuned.