Index Match equal to or less than date with multiple criteria

Hello everyone,

This is my first post. Hope someone can help.

I have created an Excel spreadsheet with a data table containing the well number, test date and production rate. I want to use the Index Match function to look up the production rate based on the day of the month. I have created the equations, but the only problem is the lookup returns the closest test date. I need it to return the closest date equal to or less than the day of the month (i.e., can not use test before it has been performed).

I am using the following equation to obtain the test date:

=INDEX(Sheet1!$B$3:$B$20,MATCH(MIN(ABS(IF(E$3=Sheet1!$A$3:$A$20,Sheet1!$B$3:$B$20-$F3,9.99999999999999E+307))),ABS(IF(E$3=Sheet1!$A$3:$A$20,Sheet1!$B$3:$B$20-$F3,9.99999999999999E+307)),0))

I have attached a copy of the spreadsheet for reference.

Any help would be greatly appreciated.

harrismlzn

I was able to come up with a fix. The equation is very long, but it gets the job done. Basically, I utilized the IF statement with the INDEX MATCH to move up to the previous test when the selected test date is later than the date of interest.

Below is the equation:

=IF(INDEX(Sheet1!$B$3:$B$20,MATCH(MIN(ABS(IF(E$3=Sheet1!$A$3:$A$20,Sheet1!$B$3:$B$20-$F3,9.99999999999999E+307))),ABS(IF(E$3=Sheet1!$A$3:$A$20,Sheet1!$B$3:$B$20-$F3,9.99999999999999E+307)),0))>F3,INDEX(Sheet1!$B$3:$B$20,MATCH(MIN(ABS(IF(E$3=Sheet1!$A$3:$A$20,Sheet1!$B$3:$B$20-$F3,9.99999999999999E+307))),ABS(IF(E$3=Sheet1!$A$3:$A$20,Sheet1!$B$3:$B$20-$F3,9.99999999999999E+307)),0)-1),INDEX(Sheet1!$B$3:$B$20,MATCH(MIN(ABS(IF(E$3=Sheet1!$A$3:$A$20,Sheet1!$B$3:$B$20-$F3,9.99999999999999E+307))),ABS(IF(E$3=Sheet1!$A$3:$A$20,Sheet1!$B$3:$B$20-$F3,9.99999999999999E+307)),0)))

I have also attached a copy of the spreadsheet for reference.

Thanks,
harrismlzn

Jacc,

Thanks for a more simplified version!

harrismlzn