Return 2 values from vlookup

[adsxvii]

in column a we have number 1, 3, 7, 12, 15 and in B respectively 10, 20, 30, 40 ,50. what I need is to be able to look up a decimal number like 2.1 that will return from A the number closest above and closest below its value, and its respective number in B. In this example it would return 1 & 10 then 3 & 20.

[ChemistB]

Assuming that your values in your first column are in accending order (like your example), I would use the INDEX and MATCH functions.
Assuming the value you are looking for is in E1 and your data is in A1:B6

Please Login or Register  to view this content.

To get the next higher value just add one to the second argument

Please Login or Register  to view this content.

Does this work for you?

ChemistB

[adsxvii]

works perfectly ty for your time

[Cheeky Charlie]

This will return misleading values if the lookup value is exactly matched.

Should include an if statement to test.

HTH

[ChemistB]

Good Point Cheeky. I'm not sure what the user wants in that case, the exact match and the one above or the one below?

ChemistB