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]

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

That is the expected behavior of Vlookup with TRUE for the matchtype.
Provided the data in 'Engine & Configuration'!D1:G24 Is sorted by Column D ASCENDING.

 

[NPK]

The array is sorted ascending, but the value returned is the one that relates to the next higher value than that associated with "1".

 

[Jonmo1]

Can you post a sample set of data that has this problem?
to include the C18 and C19, and the lookup table, and the formula...

You can use either ExcelJeanie or HTML Maker.
See the sticky post at the top of forum called "How to display your sheet on the board."

 

[Jonmo1]

here's an example setup...

A1 = 10
B1 = 9.99
D1 = A1/B1
E1 : =VLOOKUP(A1/B1,H:I,2,TRUE)
E2 : =VLOOKUP(D1,H:I,2,TRUE)

Book1
	A	B	C	D	E	F	G	H	I
1	10	9.99		1.001001		e		0	a
2						e		0.25	b
3								0.5	c
4								0.75	d
5								1	e
6								1.25	f
7								1.5	g
8								1.75	h
9								2	i
Sheet1

 

[NPK]

I appreciate your help. I had issues with the html programs on my server due to security restrictions. I attached screen shots.

The formula =IF(C36="","---",VLOOKUP(C18/C39,'Engine & Configuration'!D1:G24,2,TRUE)) appears in cell G28, highlighted in yellow.

Again, thank you for your assistance.

 

[Jonmo1]

That is returning the correct value...
C18 = 1757
C39 = 1758
1757/1758 = 0.999431171786121
The "Closest Match" to that value in your table is 0.909090909 in Cell D12
The corresonding value in E12 is 10:11

 

[Jonmo1]

perhaps you have your division backwards in the formula

YOu have

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

Perhaps you want

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

If that's the case, then
1758/1757 = 1.000569152

In which case, the closest match is 1 in Cell D13, and formula would return from E13 1:1

 

[Domenic]

If you mean that you'd like to return the closest match, whether higher or lower, try...

=INDEX('Engine & Configuration'!E2:E24,MATCH(MIN(ABS('Engine & Configuration'!D2:D24-C18/C39)),ABS('Engine & Configuration'!D2:D24-C18/C39),0))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

 

[NPK]

If you mean that you'd like to return the closest match, whether higher or lower, try...

Yes, that is what I am after. This solution did the trick. Why did this have to be confirmed with Control+Shift+Enter??

Jonmo1, thank you very much for your help. I guess the equation was doing exactly what it was supposed to be doing, but I didn't know how to ask for what I wanted it to do.

Again, thanks to both of you.