Lookup/Seach Problem?

[BenjieLop]

This is how the data table looks like:

ColumnA ColumnB
LNAME FNAME
Brown Albert
Colburn Blake
Smith James
Brown Robert
Brown Michael
Smith Bryan
Fulton Jonathan

Note that (in Column A) there are three (3) entries for the last nam
"Brown" and two (2) entries for the last name "Smith."

Here is what I want to do:

In Cell C1, enter the last name "Brown" and in Column D, it will tel
me the first names of those whose last name is "Brown."

For example, if I enter "Brown" in C1, Column D should look like this:


Albert
Robert
Michael

as these are the first names in Column B whose matching last name i
"Brown."

Similarly, if I enter "Smith" in C1, Column D should like like this:

James
Bryan

I do not have any problem with a last name that has only one entr
(e.g., Colburn or Fulton). I can easily get the first name using th
VLOOKUP formula. It is in multiple last names that I am stumped. I
there an Excel formula for this problem?

As usual, thank in advance for your time and help.

Regards

 

[David McRitchie]

Hi James,

Why not just sort the list and not hide the last names
on duplicated rows.

select A2, Ctrl+A, data, sort, column A, B, ascending, header row

Then since you appear to want count why not include a count

=IF(COUNTIF($A2:A2,A2)=1,COUNTIF(A:A,A2),"")

Other solutions can be seen in
Joining Codes in Column B to a Product in Column A (#joining)
http://www.mvps.org/dmcritchie/excel/snakecol.htm#joining
Clear Duplicate A and B column cells (#cleardupAandB)
http://www.mvps.org/dmcritchie/excel/snakecol.htm#cleardupAandB
which do require sorting of data before implementing macro.

 

[Ragdyer]

Enter this formula in D1:

=INDEX(B2:B8,SMALL(IF(A2:A8=C1,ROW(A2:A8)-1,""),ROW(A2:A8)-1))

Now, select D1.
*DO NOT* use the fill handle, but drag the *selection* down to D7.
then, <F2>, then <Ctrl> <Shift> <Enter>, to enter this as an array formula.

With a valid (existing in A2:A8) entry in C1, you should receive all the
first names from B2:B8.
You will also get #NUM! errors in column D, when the formula runs out of
first names to return.

Under normal circumstances, since you wouldn't actually know exactly how
many duplicate names exist, you will *always* want to see at least one
error, to insure that *all* occurrences have been returned.

Therefore, if no errors are returned, you should enlarge your formula cells
to cover the possibility of the number of duplicates exceeding the number of
cells you have set up to display them.

 

[BenjieLop]

RD,

<< Enter this formula in D1:

=INDEX(B2:B8,SMALL(IF(A2:A8=C1,ROW(A2:A8)-1,""),ROW(A2:A8)-1)) >>


This formula works like a charm and your further explanation on wha
else to expect is indeed a bonus. Thank you ... this formula is a
excellent one to pattern all the rest that I have to do from hereon in
I will just have to make the required adjustments on the ranges/cel
numbers.

Again, thanks a lot and more power to you.


<< Now, select D1.
*DO NOT* use the fill handle, but drag the *selection* down to D7.
then, <F2>, then <Ctrl> <Shift> <Enter>, to enter this as an arra
formula.

With a valid (existing in A2:A8) entry in C1, you should receive al
the
first names from B2:B8.
You will also get #NUM! errors in column D, when the formula runs ou
of
first names to return. >>

The #NUM! error is the least of my concerns right now. I am just s
glad that I have a formula that works. This #NUM! error will b
addressed in due time and I think I know how to do it.


<< Under normal circumstances, since you wouldn't actually know exactl
how
many duplicate names exist, you will *always* want to see at least one
error, to insure that *all* occurrences have been returned.

Therefore, if no errors are returned, you should enlarge your formul
cells
to cover the possibility of the number of duplicates exceeding th
number of
cells you have set up to display them.
--
HTH,

RD >>

Thanks again for the added tips

 

[RagDyeR]

Thanks for the feed-back.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

RD,

<< Enter this formula in D1:

=INDEX(B2:B8,SMALL(IF(A2:A8=C1,ROW(A2:A8)-1,""),ROW(A2:A8)-1)) >>


This formula works like a charm and your further explanation on what
else to expect is indeed a bonus. Thank you ... this formula is an
excellent one to pattern all the rest that I have to do from hereon in.
I will just have to make the required adjustments on the ranges/cell
numbers.

Again, thanks a lot and more power to you.


<< Now, select D1.
*DO NOT* use the fill handle, but drag the *selection* down to D7.
then, <F2>, then <Ctrl> <Shift> <Enter>, to enter this as an array
formula.

With a valid (existing in A2:A8) entry in C1, you should receive all
the
first names from B2:B8.
You will also get #NUM! errors in column D, when the formula runs out
of
first names to return. >>

The #NUM! error is the least of my concerns right now. I am just so
glad that I have a formula that works. This #NUM! error will be
addressed in due time and I think I know how to do it.


<< Under normal circumstances, since you wouldn't actually know exactly
how
many duplicate names exist, you will *always* want to see at least one
error, to insure that *all* occurrences have been returned.

Therefore, if no errors are returned, you should enlarge your formula
cells
to cover the possibility of the number of duplicates exceeding the
number of
cells you have set up to display them.
--
HTH,

RD >>

Thanks again for the added tips.