ExcelBanter - multiple match

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - multiple match (https://www.excelbanter.com/excel-worksheet-functions/253862-multiple-match.html)

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

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

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

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

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