Find value in range with two criterias, INDEX MATCH MAX

Hi guys,

I am struggling with a INDEX,MATCH and MAX formula. I am trying to retrive values from column E with two criterias:
1. That the value in column C is equal to a lookup value
2. That the date in column A is the closest before date for a lookup date

I have attatched an example file that hopefully will explain better.

I would really appreciate all help

Gnoke

Try this array formula

=INDEX(Data!E:E,MATCH(1,(DATE(RIGHT(A8,4),MID(A8,4,2),1)=Data!D:D)*(D8=Data!C:C),0))

Cmmit with Ctrl-Shift-Enter, not just Enter

Thanks for answering!

The formula works well for the sample sheet, but not with my original (much larger) dataset. It is not always the month and date values are available in both sheets. Therefor I try to search for the closest before date and not an exact match.
Any ideas?

Thanks again

Gnoke

Originally Posted by gnoke

Thanks for answering!

The formula works well for the sample sheet, but not with my original (much larger) dataset. It is not always the month and date values are available in both sheets. Therefor I try to search for the closest before date and not an exact match.
Any ideas?

Thanks again

Gnoke

Oh, I concluded that there were always dates for the 1st of each month, so I transformed the lookup dates.

May I ask the question here?
I have 2 sheets in excel with some data.
I need to find a match between two columns in each sheet and then by the recent date criteria extract the data from the third column in first sheet.