Number lookup match

[Peter1973]

I am trying to look in A1:A100 for the number that is the closest to D1 and then return the value from the corresponding cell from B1:B100. If there are 2 numbers in A1:A100 that are the closest to D1 then return the corresponding number from B1:B100 from the first one it comes to.

Hope that makes sense

Any help appreciated

 

[DRSteele]

It's ugly but it works. The formula in D3 looks for the Number that is equal to or closest to those in the Numbers column and reports the corresponding Value that is highest in the list.

	A	B	C	D
2			search for Number	35
3			closest Value	900
4
5	Number	Value
6	60	100
7	20	200
8	70	300
9	20	400
10	40	500
11	31	600
12	100	700
13	30	800
14	36	900
15	10	1000
16	80	1100
17	30	1200
18	70	1300
19	20	1400
20	70	1500
21	37	1600
22	100	1700
23	110	1800
24	20	1900
25	70	2000
26	50	2100
27	90	2200
28	20	2300
29	30	2400
30	60	2500
31	50	2600
32	70	2700
33	100	2800
34	60	2900

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet12​

Array Formulas Cell Formula D3 {=INDEX(B6:B34,MIN(IF(MIN(ABS(D2-A6:A34))=ABS(D2-A6:A34),(ROW(A6:A34)-ROW(A5)))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try to enter the {} manually yourself

<tbody>
</tbody>