Using VLOOKUP with multiple first column matches
 

I have a need to extract data from a data table that has multiple matching
first column entries. I need to be able to separate the results based on the
differing offset columns. If I use straight VLOOKUP, all I get is the first
entry in the data table. Here is a sample of what I need:

PM Date1 Date2 Index
Joe Jan 1 Feb 1 2
Bill Jan 5 Mar 4 1
Sam Feb 2 Mar 3 2
Joe Feb 8 Apr 1 3

I need to extract the unique data for Joe into another section of the
worksheet. How do I ensure that I get both entries for Joe to show up?

TIA

John Simons

Try the Google newsgroup search. Look for VLOOKUP MULTIPLE RESULTS in
microsoft.public.excel.* groups. You may find a solution there.


On Sat, 19 Feb 2005 14:51:01 -0800, "John Simons"
wrote:

I have a need to extract data from a data table that has multiple matching
first column entries. I need to be able to separate the results based on the
differing offset columns. If I use straight VLOOKUP, all I get is the first
entry in the data table. Here is a sample of what I need:

PM Date1 Date2 Index
Joe Jan 1 Feb 1 2
Bill Jan 5 Mar 4 1
Sam Feb 2 Mar 3 2
Joe Feb 8 Apr 1 3

I need to extract the unique data for Joe into another section of the
worksheet. How do I ensure that I get both entries for Joe to show up?

TIA

John Simons

With your datalist in A1 to D100
Enter name to lookup in E1.

Try this *array* formula in E2:

=INDEX(B$1:B$100,SMALL(IF($A$1:$A$1005=$E$1,ROW($A $1:$A$100)),ROW(A1)))

Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Drag across to copy to G2.
Then, select E2:G2, and drag down to copy.
Now, just how many occurrences of the lookup value (Joe) exist in the
datalist?

If you don't know the answer, you must copy down the formula *more* rows
then you anticipate that there are entries of the lookup value in the
datalist.
If this formula doesn't find a lookup value, it will return a #NUM! error.
That means that you will *always* want to see at least one row of #NUM!
errors, in order to insure that *all* the lookup values are returned.

Also, since you are returning both dates and numbers, you should make sure
that the columns containing the formulas are formatted correctly to display
the data properly.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"John Simons" wrote in message
...
I have a need to extract data from a data table that has multiple matching
first column entries. I need to be able to separate the results based on
the
differing offset columns. If I use straight VLOOKUP, all I get is the
first
entry in the data table. Here is a sample of what I need:

PM Date1 Date2 Index
Joe Jan 1 Feb 1 2
Bill Jan 5 Mar 4 1
Sam Feb 2 Mar 3 2
Joe Feb 8 Apr 1 3

I need to extract the unique data for Joe into another section of the
worksheet. How do I ensure that I get both entries for Joe to show up?

TIA

John Simons