Using Index Match Conditionally to Indicate Whether

Question

I feel pretty comfortable with INDEX MATCH, but I came across a problem I'd like to use it on where I'm not quite sure it works. I have two sheets: the first smaller sheet has a small subset of names that are on the other sheet, and has a set of dates associated with each name. The other larger sheet has a different set of dates associated with each name. What I would like to be able to do is INDEX the dates on the larger sheet and MATCH names only IF this date is greater than the date on the smaller sheet and less than the end of the month associated with that date. Rather than return the MATCH value I would like to return, say, a 1 if the criteria is met. Is this something that can be pretty easily done with INDEX MATCH or is there another set of functions I should be looking for? How would you go about this?

Answer 1

I believe your question revolves around writing an INDEX/MATCH formula around these criteria:

• INDEX the dates on the larger sheet

• MATCH names only IF this date is greater than the date on the smaller sheet

  • less than the end of the month associated with that date

• Then return 1 if criteria is met.

We can do this by nesting INDEX/MATCH inside an IF condition:

=IF(

    INDEX([LargerSheet!*Range with Dates*],

       MATCH(

         IF([SmallerSheet!*First Date in Range*]<[LargerSheet!*First Date in Range],
         [SmallerSheet!*Range with Dates]),

       [SmallerSheet!*Range with Dates*],0)

    )>0,1,"")

The MATCH function will return values for every time a match is found, and by definition, that value will be greater than 0. Hence, we want to return 1 for every occurrence of this.

Hope this helps!

Answer 2

I would use the Power Query Add-In for this. A Query can start from an Excel table, then build your data transformation in steps. You can review the result at each step.

For your requirement I would use the Merge command to join the two sheets together by name and expand the date column from your larger sheet.

http://office.microsoft.com/en-au/excel-help/merge-queries-HA104149757.aspx

This will result in all possible matches. So then I would use the Filter command to get down to just the dates in the range. You might need to edit the Filter function for this. Note there is a Power Query function to derive the End of the Month:

http://office.microsoft.com/en-au/excel-help/date-endofmonth-HA104110485.aspx?CTT=5&origin=HA104122363

Finally I would deliver the Query to a new Excel Table.