Excel – Using Index Match Conditionally to Indicate Whether

microsoft excelworksheet-function

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?

Best Answer

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!

Related Question