nearest match

[Guest]

I have in sheet 1 cell U1 1040
U2 1120
U3 1212
U4 1320
U5 1600
U6 2070

in sheet 2 i have cell F1 1010 G1 58.32
F2 1030 G2 59.29
F3 1050 G3 61.32
F4 1110 G4 64.30
F5 1140 G5 66.05
F6 1200 G6 70.10
F7 1350 G7 79.63
F8 1615 G8 96.93

I need sheet 1 U1 nearest match up or down compared to sheet 2 column F
and return the corresponding number in column G eg:

if sheet 1 U1 is 1040 use sheet 2 F2 1030 return G2 59.29
U2 is 1120 use sheet 2 F4 1110 return G4 64.30
U4 is 1320 use sheet 2 F7 1350 return G7 79.63
U5 is 1600 use sheet 2 F8 1615 return G8 96.93
-- Can any one help please
Thanks
bill gras

 

[Domenic]

On Sheet1, enter the following formula, which needs to be confirmed with
CONTROL+SHIFT=ENTER, in V1 and copy down:

=INDEX(Sheet2!$G$1:$G$8,MATCH(MIN(ABS(Sheet2!$F$1:$F$8-U1)),ABS(Sheet2!$F
$1:$F$8-U1),0))

Hope this helps!

 

[Biff]

Hi Bill!

I assume you want these formulas on sheet1 in column V.......

Entered as an array using the key combo of CTRL,SHIFT,ENTER in V1 and copied
down:

=INDEX(Sheet2!G$1:G$8,MATCH(MIN(ABS(Sheet2!F$1:F$8-U1)),ABS(Sheet2!F$1:F$8-U1),0))

Biff

 

[Guest]

Hi Domenic
Thank you for your time it works perfect

 

[Guest]

Hi Biff
Thank you for your time it works perfect