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?
Best Answer
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.