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.
Lookup Closest Value


Re: Get Closest Value In List
VLOOKUP is easy; if it's working why reinvent 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$11A2)),ABS($E$2:$E$11A2),0))
Confirmed with Ctrl+Shift+Enter (it's an array formula).
Richard


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 5ohm resistor vs 10ohm resistor is 7.5 ohms numerically (7.55 = 107.5), and 7.07 ohms ratiometrically (7.07/5 = 10/7.07)


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!