# Lookup Closest Value

• I have a list of standard resistor values on a separate tab and I want to grab the closest one (either lower or higher, whichever is closest). If I calculate a number to be 8.9, I want it to grab 9.1 instead of 8.2 (which is what it's doing with VLOOKUP). Is there an easy way to find the minimum distance or something? Thanks.

• Re: Get Closest Value In List

VLOOKUP is easy; if it's working why re-invent the wheel? Or are there other reasons you don't like VLOOKUP?

• Re: Get Closest Value In List

Hi

Here's a possibility (see attached) - assuming list of resistor vals in E2:E11 and your lookup val in A2 then one formula is:

=INDEX(\$E\$2:\$E\$11,MATCH(MIN(ABS(\$E\$2:\$E\$11-A2)),ABS(\$E\$2:\$E\$11-A2),0))

Confirmed with Ctrl+Shift+Enter (it's an array formula).

Richard

## Files

• Re: Get Closest Value In List

@Parsnip

That works! Thanks a lot, my mind was toast.

VLOOKUP is easy, but it doesn't do what I want in this case without a lot of extra work

• Re: Get Closest Value In List

I would insert a column at the left of the table containing the geometric mean of the resistor value to the right and the one above right:
[ss=
-------A------- --B--
1 Lookup Value
2 10
3 =GEOMEAN(B2,B3) 12
4 =GEOMEAN(B3,B4) 15
5 =GEOMEAN(B4,B5) 18
]*[/ss]
To get a table like this (for the two decades shown):
[ss=
--A--- --B--
1 Lookup Value
2 10
3 10.95 12
4 13.42 15
5 16.43 18
6 19.90 22
7 24.37 27
8 29.85 33
9 35.87 39
10 42.81 47
11 51.30 56
12 61.71 68
13 74.67 82
14 90.55 100
15 109.54 120
16 134.16 150
17 164.32 180
18 199.00 220
19 243.72 270
20 298.50 330
21 358.75 390
22 428.14 470
23 513.03 560
24 617.09 680
25 746.73 820
26 905.54 1000
]*[/ss]
.. and then use a VLOOUP of column A to retrieve the resistor value in column B.

Edit: Richard's (Parsnip's) method picks the closest value numerically; for this application, you want the closest value ratiometrically. For example, the breakpoint between the choice of a 5-ohm resistor vs 10-ohm resistor is 7.5 ohms numerically (7.5-5 = 10-7.5), and 7.07 ohms ratiometrically (7.07/5 = 10/7.07)

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

• Re: Get Closest Value In List

Hi Shg

I'm glad you included a good explanation but I'm still impressed I managed to understand it! - and I learned a new function! It's been a long while since I did much math/physics...

Richard

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!