Vlookup problem with a range retreiving correct number when negative

If you use VLOOKUP without specifying the 4th parameter, then it is assumed to be TRUE which means that your data has to be sorted in increasing order. Your data is sorted in columne for +ve numbers, and so I2 returns the correct result, but column F (for negative numbers) is not sorted in increasing order, so the formula in L2 gives an incorrect result.

You can use INDEX/MATCH instead, as you can specify how the data is sorted using the 3rd parameter of the MATCH function. Put this in L2:

=INDEX(G4:G12,MATCH(K2,F4:F12,-1))

Hope this helps.

Pete

That worked perfectly....I was considering to do this before, but wasn't sure if there was an easier way with vlookup…….thank you

INDEX/MATCH is more flexible than VLOOKUP, as can be seen in this example.

Glad to help, and thanks for the rep.

Pete