Thread: Match data using multiple criteria

I’m trying to match up data on separate sheets. I’ve played around with the INDEX, MATCH, and VLOOKUP functions, but I haven’t been able to make it work.

Maybe someone can help. I’ve attached an example to help explain.

Here’s the story. I routinely download data from 2 systems. The data sets have some common elements, in this case the “Employee Number” and the “Event Date.” I need a formula in each row on the Consolidated tab that will look at these two elements from Source 1 tab, identify the matching row in the data from Source 2 tab, and produce the “Closing Date” from Source 2.

Any help would be appreciated.

Sean,

In C2 on sheet 'Consolidated' use the following array formula:

=INDEX('Source 2'!$C$2:$C$7,MATCH(A2&B2,'Source 2'!$A$2:$A$7&'Source 2'!$B$2:$B$7,0))

This formula must be confirmed with CTRL+SHIFT+ENTER, not just ENTER. When done properly Excel will automatically insert braces, { } , around your formula.