Lookup Formula Not Erroring Out When Value Not Found Looking Up Approximate Value

[excel_learner]

I have dates ranges (not all months individually) e.g 01/07/2010 - 30/06/2011, while applying lookup formula I get the last value while looking up for a date within the range even if the date is not within the range. I have attached the sample sheet for easy reference.

Thanks

[Excel Fox]

If you look at the LOOKUP function - Excel - Office.com explained by microsoft, it is clear that If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value

For your question specifically, your formula could be =INDEX($B$2:$B$9,MATCH(A12,$A$2:$A$9,0))

[excel_learner]

Thanks, however, this formula brings only the values if they are present in the range, if not it says N/A#. If I extend the date ranges in lookup formula then it works as it shows in attached sheet.

Thanks

[Excel Fox]

Just use the IFERROR function

=IFERROR(INDEX($B$2:$B$9,MATCH(A12,$A$2:$A$9,0))," Date Not Found")

[excel_learner]

Actually I wanted to work within the ranges of dates, i.e if the date falls within 01/07/2010 - 30/06/2011 if the value in B column is 0.3 and date is 29/06/2011 or 02/07/2010 then it should bring 0.3, which I achieved through extending the dates ranges in lookup formula itself. But if there is any simple way to do that, kindly let me know as some time date ranges are many and individual date would be difficult.

Hope I have clarified this now.

Thanks

[Excel Fox]

You can do it using =INDEX($B$2:$B$25,MATCH(B28,$A$2:$A$25,1))