lookup closest match

[Duritz]

Hi - if I want to lookup a value in a list, and return the closest match to it, how do I do it?

Example - I want to find the closest match to the value 1600 in this list -

1000
1250
2350
4000
8500


So, the closest match is 1250. How do I return this value?

 

[Jon von der Heyden]

Hi,

Try:

=LOOKUP(1600,{1000,1250,2350,4000,8500})

and you can change 1600 to a cell reference (e.g. A1) and {1000,1250,2350,4000,8500} to a table array (e.g. B1:B100).

Regards
Jon

 

[Duritz]

Yeah but that will always match to the closest value BELOW the value in question, won't it? So, if the value to lookup was 1600, and the array was -

1100
1650
2000

wouldn't it return 1100 as the closest? What I need is the closest value in that array.

 

[Jon von der Heyden]

Hmmm yes, I seem to have missed the point. Do you need a singluar formula or could you do it in steps? Stepped approach as follows:

Book4
	B	C	D	E	F	G	H
23	1600		2	1100	500		1650
24			3	1650	50
25	850
26	1100
27	1650
28	1900
Sheet1

Or for an ugly singluar formula:

=IF(ABS($B$23-MIN(IF(B25:B28>B23,B25:B28)))<ABS($B$23-MAX(IF(B25:B28<B23,B25:B28))),MIN(IF(B25:B28>B23,B25:B28)),MAX(IF(B25:B28<B23,B25:B28)))

Confirmed with Ctrl+Shift+Enter

Regards,
Jon

 

[Duritz]

Yes I can follow that - it's a workable solution, thanks for your help.

 

[barry houdini]

If your comparison values A1:A5 and you want to find the closest match to a value in B1 try

=INDEX(A1:A5,MATCH(MIN(ABS(B1-A1:A5)),ABS(B1-A1:A5),0))

confirmed with CTRL+SHIFT+ENTER

 

[Domenic]

Here's another way...

=INDEX(A2:A100,MATCH(MIN(ABS(A2:A100-B2)),ABS(A2:A100-B2),0))

...where B2 contains the value of interest, such as 1600. Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER. Adjust the range accordingly.

Hope this helps!

 

[Jon von der Heyden]

Slick