Return Corresponding Date of a Value in a Particular Month/Year

[Jonathan78]

Hello all,

The following formula searches in range START!O7:O1500, for the value given in F27
As a result it returns the date next to it in range START!D7:D1500
It returns the first match it finds.

Please Login or Register  to view this content.

I'd like to add a second condition.
Cell H5 contains a month number (like: 5 or 6 etc)
Cell H6 contains a year number (like: 2012 or 2013 etc)
Is it possible to to adjust the formula so it will return the first match in a particular month/year

[Pauleyb]

Probably the easiest solution is not the prettiest. Create a helper column in your data table which is a concatenation of the data you want to search for. I assume there is a month and year in your big data table in the START sheet. I'll assume the Month is in P and the Year is in Q. I'll also assume the new column is Z. So, for example, in Z7 you would enter:
=O7&P7&Q7
you would drag that down to your last row (1500)
then your equation would become:
=INDEX(START!D7:D1500;MATCH(F27&H5&H6;START!Z7:Z1500;FALSE);1)