Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
same cell from multiple sheets into one column | Excel Discussion (Misc queries) | |||
Can VLOOKUP return multiple answers based on several identical lo. | Excel Worksheet Functions | |||
Can vlookup return multiple matches in a single cell? | Excel Discussion (Misc queries) | |||
vlookup change column index position - without changing formulae | Excel Worksheet Functions | |||
Multiple Vlookup? | Excel Worksheet Functions |