Finding second value?
 

$1B3 thru B20 has names of people. $1C3 thru C20 has numbers. There are
some duplicate names in B3 thru B20. When I enter name in $2D4 I would like
$2E4 to have number from $C3 thru C20 that matches name. More importantly,
I would like $2E5 to show the second corresponding number to the same name.
I can get the first match with VLOOKUP. I am having difficulty getting
second number.

Thanks
Bobby

Finding second value?
 

Create a unique reference for each name in a helper column, for
example put this formula in F3:

=IF(B3="","",B3&"_"&COUNTIF(B$3:B3,B3))

and copy down to F20. Then with a name in D4, put this formula in E4:

=IF(ISNA(MATCH(D$4&"_"&ROW(E1),F$3:F$20,0)),"",IND EX(C$3:C$20,MATCH(D
$4&"_"&ROW(E1),F$3:F$20,0)))

and then copy this down for as many rows as you think there may be
duplicates (eg to E7).

Hope this helps.

Pete


On Aug 17, 6:27*am, bgkgmg wrote:
$1B3 thru B20 has names of people. *$1C3 thru C20 has numbers. *There are
some duplicate names in B3 thru B20. *When I enter name in $2D4 I would like
$2E4 to have *number from $C3 thru C20 that matches name. *More importantly,
I would like $2E5 to show the second corresponding number to the same name. *
I can get the first match with VLOOKUP. *I am having difficulty getting
second number.

Thanks
Bobby

Finding second value?
 

There are a couple of ways to do this shown on this page:
http://www.techonthenet.com/excel/formulas/vlookup.php
one of them is pretty much the same as offered by Pete_UK, while down near
the bottom of the page is an array formula method that doesn't require the
helper column. I personally think that the use of the helper column, as
Pete_UK has suggested, is probably a more understandable setup for anyone
coming along later and trying to "maintain" or understand just exactly what's
going on.

"bgkgmg" wrote:

$1B3 thru B20 has names of people. $1C3 thru C20 has numbers. There are
some duplicate names in B3 thru B20. When I enter name in $2D4 I would like
$2E4 to have number from $C3 thru C20 that matches name. More importantly,
I would like $2E5 to show the second corresponding number to the same name.
I can get the first match with VLOOKUP. I am having difficulty getting
second number.

Thanks
Bobby

Finding second value?
 

Thanks for response and using helper cells has helped on other situations.
I copied your formula into E4
=IF(ISNA(MATCH(D$4&"_"&ROW(E1),F$3:F$20,0)),"",IND EX(C$3:C$20,MATCH(D$4&"_"&ROW(E1),F$3:F$20,0)))
then into E5. What am I changing to this formula in E5 to find the second
match? Also what is meaning $ when you use. I used as short for sheet.
Where you just copying my entry or is it used for other purpose.

I appreciate your help
Thanks
Bobby


"Pete_UK" wrote:

Create a unique reference for each name in a helper column, for
example put this formula in F3:

=IF(B3="","",B3&"_"&COUNTIF(B$3:B3,B3))

and copy down to F20. Then with a name in D4, put this formula in E4:

=IF(ISNA(MATCH(D$4&"_"&ROW(E1),F$3:F$20,0)),"",IND EX(C$3:C$20,MATCH(D
$4&"_"&ROW(E1),F$3:F$20,0)))

and then copy this down for as many rows as you think there may be
duplicates (eg to E7).

Hope this helps.

Pete


On Aug 17, 6:27 am, bgkgmg wrote:
$1B3 thru B20 has names of people. $1C3 thru C20 has numbers. There are
some duplicate names in B3 thru B20. When I enter name in $2D4 I would like
$2E4 to have number from $C3 thru C20 that matches name. More importantly,
I would like $2E5 to show the second corresponding number to the same name.
I can get the first match with VLOOKUP. I am having difficulty getting
second number.

Thanks
Bobby

Finding second value?
 

Until Pete_UK gives you some more information on how it's working, I think
that a explanation of the use of the $ symbol (and lack of using it) in
addressing will help you understand a little more.
When used with a cell address, the $ symbol makes the address Absolute.
Absolute means unchanging as the address is copied or filled to other cells
on the sheet. Either the column part or the row part of an address, or both
parts, may be made absolute or left relative.

Try some experiments and you'll understand better.

Enter a series in row 1 of a sheet, simpy 1 2 3 4 5 at A1, B1, C1, D1 and E1
will do.

Now down below at F2 enter this formula:
=E1
fill that formula to the left all the way to column A. Notice that you get
this as a result:
#REF! 1 2 3 4 5
=E1 was a completely relative address. As you filled it to the left, the
column portion automatically changed by one column, so you got the series
from row 1 until you filled the formula into column A, when you got the #REF!
error, because there is no column to the left of column A.

Now select F2 and fill it down the column into F3, F4 and F5. Notice that
is now shows
5
4
and then goes to zeros, it changed to =E2 in F3 which was 4 from the formula
in E2, and went to zeros because there's nothing below E2 on the sheet.

Try the experiments using =$E1, =E$1 and =$E$1 as the starting formula in F2
instead of just =E2 and observe the different results you get.




"bgkgmg" wrote:

Thanks for response and using helper cells has helped on other situations.
I copied your formula into E4
=IF(ISNA(MATCH(D$4&"_"&ROW(E1),F$3:F$20,0)),"",IND EX(C$3:C$20,MATCH(D$4&"_"&ROW(E1),F$3:F$20,0)))
then into E5. What am I changing to this formula in E5 to find the second
match? Also what is meaning $ when you use. I used as short for sheet.
Where you just copying my entry or is it used for other purpose.

I appreciate your help
Thanks
Bobby


"Pete_UK" wrote:

Create a unique reference for each name in a helper column, for
example put this formula in F3:

=IF(B3="","",B3&"_"&COUNTIF(B$3:B3,B3))

and copy down to F20. Then with a name in D4, put this formula in E4:

=IF(ISNA(MATCH(D$4&"_"&ROW(E1),F$3:F$20,0)),"",IND EX(C$3:C$20,MATCH(D
$4&"_"&ROW(E1),F$3:F$20,0)))

and then copy this down for as many rows as you think there may be
duplicates (eg to E7).

Hope this helps.

Pete


On Aug 17, 6:27 am, bgkgmg wrote:
$1B3 thru B20 has names of people. $1C3 thru C20 has numbers. There are
some duplicate names in B3 thru B20. When I enter name in $2D4 I would like
$2E4 to have number from $C3 thru C20 that matches name. More importantly,
I would like $2E5 to show the second corresponding number to the same name.
I can get the first match with VLOOKUP. I am having difficulty getting
second number.

Thanks
Bobby

Finding second value?
 

Once I understood $ I was able to make Petes formula work.
Thanks

"JLatham" wrote:

Until Pete_UK gives you some more information on how it's working, I think
that a explanation of the use of the $ symbol (and lack of using it) in
addressing will help you understand a little more.
When used with a cell address, the $ symbol makes the address Absolute.
Absolute means unchanging as the address is copied or filled to other cells
on the sheet. Either the column part or the row part of an address, or both
parts, may be made absolute or left relative.

Try some experiments and you'll understand better.

Enter a series in row 1 of a sheet, simpy 1 2 3 4 5 at A1, B1, C1, D1 and E1
will do.

Now down below at F2 enter this formula:
=E1
fill that formula to the left all the way to column A. Notice that you get
this as a result:
#REF! 1 2 3 4 5
=E1 was a completely relative address. As you filled it to the left, the
column portion automatically changed by one column, so you got the series
from row 1 until you filled the formula into column A, when you got the #REF!
error, because there is no column to the left of column A.

Now select F2 and fill it down the column into F3, F4 and F5. Notice that
is now shows
5
4
and then goes to zeros, it changed to =E2 in F3 which was 4 from the formula
in E2, and went to zeros because there's nothing below E2 on the sheet.

Try the experiments using =$E1, =E$1 and =$E$1 as the starting formula in F2
instead of just =E2 and observe the different results you get.




"bgkgmg" wrote:

Thanks for response and using helper cells has helped on other situations.
I copied your formula into E4
=IF(ISNA(MATCH(D$4&"_"&ROW(E1),F$3:F$20,0)),"",IND EX(C$3:C$20,MATCH(D$4&"_"&ROW(E1),F$3:F$20,0)))
then into E5. What am I changing to this formula in E5 to find the second
match? Also what is meaning $ when you use. I used as short for sheet.
Where you just copying my entry or is it used for other purpose.

I appreciate your help
Thanks
Bobby


"Pete_UK" wrote:

Create a unique reference for each name in a helper column, for
example put this formula in F3:

=IF(B3="","",B3&"_"&COUNTIF(B$3:B3,B3))

and copy down to F20. Then with a name in D4, put this formula in E4:

=IF(ISNA(MATCH(D$4&"_"&ROW(E1),F$3:F$20,0)),"",IND EX(C$3:C$20,MATCH(D
$4&"_"&ROW(E1),F$3:F$20,0)))

and then copy this down for as many rows as you think there may be
duplicates (eg to E7).

Hope this helps.

Pete


On Aug 17, 6:27 am, bgkgmg wrote:
$1B3 thru B20 has names of people. $1C3 thru C20 has numbers. There are
some duplicate names in B3 thru B20. When I enter name in $2D4 I would like
$2E4 to have number from $C3 thru C20 that matches name. More importantly,
I would like $2E5 to show the second corresponding number to the same name.
I can get the first match with VLOOKUP. I am having difficulty getting
second number.

Thanks
Bobby

Finding second value?
 

Glad to hear that. Always nice to know that I played some small part in a
successful team effort.

Sometimes it's not just the "what" of a thing that you need to understand
but also the "why" or "how" of it. Especially when trying to adapt a general
tool to a specific use. What you've learned here should serve you well in
your future work with Excel worksheet formulas.

"bgkgmg" wrote:

Once I understood $ I was able to make Petes formula work.
Thanks

"JLatham" wrote:

Until Pete_UK gives you some more information on how it's working, I think
that a explanation of the use of the $ symbol (and lack of using it) in
addressing will help you understand a little more.
When used with a cell address, the $ symbol makes the address Absolute.
Absolute means unchanging as the address is copied or filled to other cells
on the sheet. Either the column part or the row part of an address, or both
parts, may be made absolute or left relative.

Try some experiments and you'll understand better.

Enter a series in row 1 of a sheet, simpy 1 2 3 4 5 at A1, B1, C1, D1 and E1
will do.

Now down below at F2 enter this formula:
=E1
fill that formula to the left all the way to column A. Notice that you get
this as a result:
#REF! 1 2 3 4 5
=E1 was a completely relative address. As you filled it to the left, the
column portion automatically changed by one column, so you got the series
from row 1 until you filled the formula into column A, when you got the #REF!
error, because there is no column to the left of column A.

Now select F2 and fill it down the column into F3, F4 and F5. Notice that
is now shows
5
4
and then goes to zeros, it changed to =E2 in F3 which was 4 from the formula
in E2, and went to zeros because there's nothing below E2 on the sheet.

Try the experiments using =$E1, =E$1 and =$E$1 as the starting formula in F2
instead of just =E2 and observe the different results you get.




"bgkgmg" wrote:

Thanks for response and using helper cells has helped on other situations.
I copied your formula into E4
=IF(ISNA(MATCH(D$4&"_"&ROW(E1),F$3:F$20,0)),"",IND EX(C$3:C$20,MATCH(D$4&"_"&ROW(E1),F$3:F$20,0)))
then into E5. What am I changing to this formula in E5 to find the second
match? Also what is meaning $ when you use. I used as short for sheet.
Where you just copying my entry or is it used for other purpose.

I appreciate your help
Thanks
Bobby


"Pete_UK" wrote:

Create a unique reference for each name in a helper column, for
example put this formula in F3:

=IF(B3="","",B3&"_"&COUNTIF(B$3:B3,B3))

and copy down to F20. Then with a name in D4, put this formula in E4:

=IF(ISNA(MATCH(D$4&"_"&ROW(E1),F$3:F$20,0)),"",IND EX(C$3:C$20,MATCH(D
$4&"_"&ROW(E1),F$3:F$20,0)))

and then copy this down for as many rows as you think there may be
duplicates (eg to E7).

Hope this helps.

Pete


On Aug 17, 6:27 am, bgkgmg wrote:
$1B3 thru B20 has names of people. $1C3 thru C20 has numbers. There are
some duplicate names in B3 thru B20. When I enter name in $2D4 I would like
$2E4 to have number from $C3 thru C20 that matches name. More importantly,
I would like $2E5 to show the second corresponding number to the same name.
I can get the first match with VLOOKUP. I am having difficulty getting
second number.

Thanks
Bobby