Find Approximate Match

[nvallev]

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]

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]

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.

[zbor]

You just need to sort reference table. Values that will be updated don't need to be sorted.

[nvallev]

Ok. Thank you very much!