Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple match
i have in sheet K column C row 3 to row 240 names eg:
sheet K C 3 joe 4 adam 5 reon 6 bill 7 ian and so on in sheet RC column A row 2 to row 1001 i have multiple names of the same names as in sheet K , and in columns BB and BC i have values eg: sheet RC A BB BC 2 joe 8.3 3 joe 8.3 120 4 joe 8.3 5 joe 8.3 6 joe 8.3 7 adam 0.2 140 8 adam 0.2 9 reon 10 reon 11 bill 3.7 12 bill 3.7 13 bill 3.7 90 14 bill 3.7 15 ian 2.9 110 16 ian 2.9 down to 1001 rows (columns are at random sequences) i need to have a name in sheet K to match the names in sheet RC and return what is in columns BB ans BC to columns k and L in sheet K eg: sheet K C k L 3 joe 8.3 120 4 adam 0.2 140 5 reon 6 reon 7 bill 3.7 90 8 ian 2.9 110 and so on can some one help me please ? bill gras |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple match
In Sheet K
K3: =IF(INDEX('Sheet RC'!BB:BB,MATCH(C3,'Sheet RC'!A:A,0))=0,"",INDEX('Sheet RC'!BB:BB,MATCH(C3,'Sheet RC'!A:A,0))) L3: =IF(K3="","",LOOKUP(2,1/(('Sheet RC'!A:A=C3)*('Sheet RC'!BC:BC<"")),'Sheet RC'!BC:BC)) Select K3 and L3 and copy down as far as needed. "bill gras" wrote: i have in sheet K column C row 3 to row 240 names eg: sheet K C 3 joe 4 adam 5 reon 6 bill 7 ian and so on in sheet RC column A row 2 to row 1001 i have multiple names of the same names as in sheet K , and in columns BB and BC i have values eg: sheet RC A BB BC 2 joe 8.3 3 joe 8.3 120 4 joe 8.3 5 joe 8.3 6 joe 8.3 7 adam 0.2 140 8 adam 0.2 9 reon 10 reon 11 bill 3.7 12 bill 3.7 13 bill 3.7 90 14 bill 3.7 15 ian 2.9 110 16 ian 2.9 down to 1001 rows (columns are at random sequences) i need to have a name in sheet K to match the names in sheet RC and return what is in columns BB ans BC to columns k and L in sheet K eg: sheet K C k L 3 joe 8.3 120 4 adam 0.2 140 5 reon 6 reon 7 bill 3.7 90 8 ian 2.9 110 and so on can some one help me please ? bill gras |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple match
Bill,
You could use VLOOKUP with False as the fourth parameter to return the value from BB, and SUMIF to return the value from BC. HTH, Bernie MS Excel MVP "bill gras" wrote in message ... i have in sheet K column C row 3 to row 240 names eg: sheet K C 3 joe 4 adam 5 reon 6 bill 7 ian and so on in sheet RC column A row 2 to row 1001 i have multiple names of the same names as in sheet K , and in columns BB and BC i have values eg: sheet RC A BB BC 2 joe 8.3 3 joe 8.3 120 4 joe 8.3 5 joe 8.3 6 joe 8.3 7 adam 0.2 140 8 adam 0.2 9 reon 10 reon 11 bill 3.7 12 bill 3.7 13 bill 3.7 90 14 bill 3.7 15 ian 2.9 110 16 ian 2.9 down to 1001 rows (columns are at random sequences) i need to have a name in sheet K to match the names in sheet RC and return what is in columns BB ans BC to columns k and L in sheet K eg: sheet K C k L 3 joe 8.3 120 4 adam 0.2 140 5 reon 6 reon 7 bill 3.7 90 8 ian 2.9 110 and so on can some one help me please ? bill gras |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple match
Hi Teethless mama
Thank you for your time ,but the work sheet functions you gave me returned blank cells Does it make a difference because I'm using excel 2000 ? -- bill gras "Teethless mama" wrote: In Sheet K K3: =IF(INDEX('Sheet RC'!BB:BB,MATCH(C3,'Sheet RC'!A:A,0))=0,"",INDEX('Sheet RC'!BB:BB,MATCH(C3,'Sheet RC'!A:A,0))) L3: =IF(K3="","",LOOKUP(2,1/(('Sheet RC'!A:A=C3)*('Sheet RC'!BC:BC<"")),'Sheet RC'!BC:BC)) Select K3 and L3 and copy down as far as needed. "bill gras" wrote: i have in sheet K column C row 3 to row 240 names eg: sheet K C 3 joe 4 adam 5 reon 6 bill 7 ian and so on in sheet RC column A row 2 to row 1001 i have multiple names of the same names as in sheet K , and in columns BB and BC i have values eg: sheet RC A BB BC 2 joe 8.3 3 joe 8.3 120 4 joe 8.3 5 joe 8.3 6 joe 8.3 7 adam 0.2 140 8 adam 0.2 9 reon 10 reon 11 bill 3.7 12 bill 3.7 13 bill 3.7 90 14 bill 3.7 15 ian 2.9 110 16 ian 2.9 down to 1001 rows (columns are at random sequences) i need to have a name in sheet K to match the names in sheet RC and return what is in columns BB ans BC to columns k and L in sheet K eg: sheet K C k L 3 joe 8.3 120 4 adam 0.2 140 5 reon 6 reon 7 bill 3.7 90 8 ian 2.9 110 and so on can some one help me please ? bill gras |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
multiple match
Hi Teethless mama
Thank you for your time , the first formula in sheet K cell K3 is great The second formula in sheet K cell L3 returns a #NUM! could you have a look for me please , I have checked to see if I made a mistake , but I did not. -- bill gras "Teethless mama" wrote: In Sheet K K3: =IF(INDEX('Sheet RC'!BB:BB,MATCH(C3,'Sheet RC'!A:A,0))=0,"",INDEX('Sheet RC'!BB:BB,MATCH(C3,'Sheet RC'!A:A,0))) L3: =IF(K3="","",LOOKUP(2,1/(('Sheet RC'!A:A=C3)*('Sheet RC'!BC:BC<"")),'Sheet RC'!BC:BC)) Select K3 and L3 and copy down as far as needed. "bill gras" wrote: i have in sheet K column C row 3 to row 240 names eg: sheet K C 3 joe 4 adam 5 reon 6 bill 7 ian and so on in sheet RC column A row 2 to row 1001 i have multiple names of the same names as in sheet K , and in columns BB and BC i have values eg: sheet RC A BB BC 2 joe 8.3 3 joe 8.3 120 4 joe 8.3 5 joe 8.3 6 joe 8.3 7 adam 0.2 140 8 adam 0.2 9 reon 10 reon 11 bill 3.7 12 bill 3.7 13 bill 3.7 90 14 bill 3.7 15 ian 2.9 110 16 ian 2.9 down to 1001 rows (columns are at random sequences) i need to have a name in sheet K to match the names in sheet RC and return what is in columns BB ans BC to columns k and L in sheet K eg: sheet K C k L 3 joe 8.3 120 4 adam 0.2 140 5 reon 6 reon 7 bill 3.7 90 8 ian 2.9 110 and so on can some one help me please ? bill gras |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find a Match in Multiple Places & Return Multiple Values | Excel Worksheet Functions | |||
MATCH Multiple Criteria & Return Previous / Penultimate Match | Excel Worksheet Functions | |||
Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple rec | Excel Discussion (Misc queries) | |||
How do I add multiple values that match multiple conditions? | Excel Discussion (Misc queries) |