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


    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)

Participate now!

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