software

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

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!

In article gt;,
bill gras gt; wrote:

gt; I have in sheet 1 cell U1 1040
gt; U2 1120
gt; U3 1212
gt; U4 1320
gt; U5 1600
gt; U6 2070
gt;
gt; in sheet 2 i have cell F1 1010 G1 58.32
gt; F2 1030 G2 59.29
gt; F3 1050 G3 61.32
gt; F4 1110 G4 64.30
gt; F5 1140 G5 66.05
gt; F6 1200 G6 70.10
gt; F7 1350 G7 79.63
gt; F8 1615 G8 96.93
gt;
gt; I need sheet 1 U1 nearest match up or down compared to sheet 2 column F
gt; and return the corresponding number in column G eg:
gt;
gt; if sheet 1 U1 is 1040 use sheet 2 F2 1030 return G2 59.29
gt; U2 is 1120 use sheet 2 F4 1110 return G4 64.30
gt; U4 is 1320 use sheet 2 F7 1350 return G7 79.63
gt; U5 is 1600 use sheet 2 F8 1615 return G8 96.93
gt; -- Can any one help please
gt; Thanks
gt; bill gras

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

quot;bill grasquot; gt; wrote in message
...
gt;I have in sheet 1 cell U1 1040
gt; U2 1120
gt; U3 1212
gt; U4 1320
gt; U5 1600
gt; U6 2070
gt;
gt; in sheet 2 i have cell F1 1010 G1 58.32
gt; F2 1030 G2 59.29
gt; F3 1050 G3 61.32
gt; F4 1110 G4 64.30
gt; F5 1140 G5 66.05
gt; F6 1200 G6 70.10
gt; F7 1350 G7 79.63
gt; F8 1615 G8 96.93
gt;
gt; I need sheet 1 U1 nearest match up or down compared to sheet 2 column F
gt; and return the corresponding number in column G eg:
gt;
gt; if sheet 1 U1 is 1040 use sheet 2 F2 1030 return G2 59.29
gt; U2 is 1120 use sheet 2 F4 1110 return G4 64.30
gt; U4 is 1320 use sheet 2 F7 1350 return G7 79.63
gt; U5 is 1600 use sheet 2 F8 1615 return G8 96.93
gt; -- Can any one help please
gt; Thanks
gt; bill gras
Hi Domenic
Thank you for your time it works perfect
--
bill grasquot;Domenicquot; wrote:

gt; On Sheet1, enter the following formula, which needs to be confirmed with
gt; CONTROL SHIFT=ENTER, in V1 and copy down:
gt;
gt; =INDEX(Sheet2!$G$1:$G$8,MATCH(MIN(ABS(Sheet2!$F$1: $F$8-U1)),ABS(Sheet2!$F
gt; $1:$F$8-U1),0))
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; bill gras gt; wrote:
gt;
gt; gt; I have in sheet 1 cell U1 1040
gt; gt; U2 1120
gt; gt; U3 1212
gt; gt; U4 1320
gt; gt; U5 1600
gt; gt; U6 2070
gt; gt;
gt; gt; in sheet 2 i have cell F1 1010 G1 58.32
gt; gt; F2 1030 G2 59.29
gt; gt; F3 1050 G3 61.32
gt; gt; F4 1110 G4 64.30
gt; gt; F5 1140 G5 66.05
gt; gt; F6 1200 G6 70.10
gt; gt; F7 1350 G7 79.63
gt; gt; F8 1615 G8 96.93
gt; gt;
gt; gt; I need sheet 1 U1 nearest match up or down compared to sheet 2 column F
gt; gt; and return the corresponding number in column G eg:
gt; gt;
gt; gt; if sheet 1 U1 is 1040 use sheet 2 F2 1030 return G2 59.29
gt; gt; U2 is 1120 use sheet 2 F4 1110 return G4 64.30
gt; gt; U4 is 1320 use sheet 2 F7 1350 return G7 79.63
gt; gt; U5 is 1600 use sheet 2 F8 1615 return G8 96.93
gt; gt; -- Can any one help please
gt; gt; Thanks
gt; gt; bill gras
gt;

Hi Biff
Thank you for your time it works perfect
--
bill grasquot;Biffquot; wrote:

gt; Hi Bill!
gt;
gt; I assume you want these formulas on sheet1 in column V.......
gt;
gt; Entered as an array using the key combo of CTRL,SHIFT,ENTER in V1 and copied
gt; down:
gt;
gt; =INDEX(Sheet2!G$1:G$8,MATCH(MIN(ABS(Sheet2!F$1:F$8-U1)),ABS(Sheet2!F$1:F$8-U1),0))
gt;
gt; Biff
gt;
gt; quot;bill grasquot; gt; wrote in message
gt; ...
gt; gt;I have in sheet 1 cell U1 1040
gt; gt; U2 1120
gt; gt; U3 1212
gt; gt; U4 1320
gt; gt; U5 1600
gt; gt; U6 2070
gt; gt;
gt; gt; in sheet 2 i have cell F1 1010 G1 58.32
gt; gt; F2 1030 G2 59.29
gt; gt; F3 1050 G3 61.32
gt; gt; F4 1110 G4 64.30
gt; gt; F5 1140 G5 66.05
gt; gt; F6 1200 G6 70.10
gt; gt; F7 1350 G7 79.63
gt; gt; F8 1615 G8 96.93
gt; gt;
gt; gt; I need sheet 1 U1 nearest match up or down compared to sheet 2 column F
gt; gt; and return the corresponding number in column G eg:
gt; gt;
gt; gt; if sheet 1 U1 is 1040 use sheet 2 F2 1030 return G2 59.29
gt; gt; U2 is 1120 use sheet 2 F4 1110 return G4 64.30
gt; gt; U4 is 1320 use sheet 2 F7 1350 return G7 79.63
gt; gt; U5 is 1600 use sheet 2 F8 1615 return G8 96.93
gt; gt; -- Can any one help please
gt; gt; Thanks
gt; gt; bill gras
gt;
gt;
gt;