Vlookup for a range

[montanaaggie]

In column A, I have the numbers 1, 2, 3, 4 going down
In column D, I have the numbers .8, 1.1, 1.2, 1.3 going down
In column E, I have the numbers 5, 10, 15, 20 going down

In cell G1, I want to have a vlookup, looking for the value in column A and finding the appropriate match in column D. Right now the way vlookup works for 1 (Cell A1), is its going to bring back 5, because .8<1. I would like it to bring back 10 as the difference between 1.1-1 or .1 is less than 1-.8 or .2 Is there a way to modify my vlookup equation to do this.

 

[cb366374]

I'm not sure I undertand what you're trying to accomplish exactly but I can guess...

Vlookup rounds down to the next largest value if it doesn't find an exact match and you want it to find the closest match above or below. I dont' think this is possible with vlookup but something like this may work.

=INDEX($E$1:$E$15,MATCH(MIN(ABS($D$1:$D$15-A1)),ABS($D$1:$D$15-A1),0))

EDIT: I forgot to mention this is written as an array formula so you'll have to use Ctrl + Shift + Enter - Props to Barry Houdini