Get row number for the row matching multiple criteria in Excel

Question

I have 2 worksheet that work together. Worksheet "Application" has a DATE column, a CODE column (a single digit number), and a VALUE column with a dollar value.

Worksheet "Scheduled" also has a DATE column.

I need a formula on the Scheduled worksheet that will look at the Application worksheet, find the row that has both a given date and a given code, then return the 1st row number meeting both of those criteria. I can get the row number for one criteria or the other but not both. For example:

=MATCH(D4,Application!C$1:C$279)

Will return the row number with the date that is in D4. But how do I add a second criteria to that?

Answer 1

I would use the Power Query Add-In for this. It has a Merge command that can handle this requirement without writing a single formula or any code.

http://office.microsoft.com/en-au/excel-help/merge-queries-HA104149757.aspx?CTT=5&origin=HA103993872

I would start a Query based on your Application table. I would add an Index column (row number).

Then I would start another Query based on your Scheduled table. I would Merge that with the Application query, matching on Date and Code. Then I would use the Group By command to collapse by Date and Code and get the Min Index.

Answer 2

You can combine criteria with an array formula, too. If your second criteria is in E4 and the corresponding column is Application!D$1:D$279, then the following formula would work. It must be entered with Ctrl+Shift+Enter as it is an array formula.

{=MATCH(D4&"|"&E4,Application!C$1:C$279&"|"&Application!D$1:D$279)}