VLOOKUP to closest match

[NPK]

I've got a vlookup function driven by the result of a simple equation.

=IF(C36="","---",VLOOKUP(C18/C39,'Engine & Configuration'!D1:G24,2,TRUE))

The issue I have is that I want the result to return the CLOSEST match, not an exact or rounded up match. For example, is my "C18/C39" equation returns a value of 1.000039, I want it to match with "1" in the array and not with 1.25. The FALSE option will not work because exact matches will be very rare.

The values in the array are not whole numbers, so I don't believe a ROUNDUP type code will help.

Thanks.

 

[Jonmo1]

Domenic - Wow!!, cool solution.

Nich, I guess it's just a matter of fully comprehending what Excel considers the "Closest" match...

In vlookup, the closest match is
The LARGEST value that is LESS THAN or EQUAL to the Lookup Value.

 

[Domenic]

Why did this have to be confirmed with Control+Shift+Enter??

It tells Excel that we're dealing with an array formula. For more information, have a look at Excel's help file under 'array formula'.

 

[NPK]

Again, thanks a ton to both. It is a bit tricky to move that equation around as well since it needs to know the array information.

Nich