| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
nearest match
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
nearest match
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 , bill gras wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
nearest match
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 "bill gras" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
nearest match
Hi Domenic
Thank you for your time it works perfect -- bill gras "Domenic" wrote: 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 , bill gras wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
nearest match
Hi Biff
Thank you for your time it works perfect -- bill gras "Biff" wrote: 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 "bill gras" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match Index | Excel Worksheet Functions | |||
| Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
| Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
| Find a match that;s not exact | Excel Worksheet Functions | |||
| Vlookup, Index & Match | Excel Worksheet Functions | |||
Linear Mode
