Find Approximate Match

**nvallev** · 07-06-2011, 06:18 AM

Dear all,

I have been trying to use vlookup to fin an approximate value and return the value in another column and keep getting NA error.

I have a range A1:A20 with numbers ranging from 0 to 1 and a range B1:B20 with sales. What I want to do is having in another part of the spreadsheet a list of values to lookup for example 0.2, 0.4, 0.6 and use the formula to look it up en A1:A20 and return the values en B1:b20 with an approximate match.

I have read it can be done combining Index and Match functions but I don't know how.

Thanks.

**Colin Legg** · 07-06-2011, 07:05 AM

Hi and welcome to the forum.

If A1:A20 is sorted ascending then you can use VLOOKUP (or INDEX and MATCH) by passing TRUE into the 4th, "Range_Lookup" parameter. When doing this, it will look for the largest value in A1:A20 which is less than or equal to the look up value. See attached example.

**nvallev** · 07-06-2011, 07:11 AM

Thank you for your answer.

Is there any way I can do it without having to sort the ranges?

The ranges will regularly be updated with data from other sheets in the workbook so I would like to do it without having to sort every time the data changes in the ranges

Thanks.