Lookup Closest Value

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

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)

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