Expert: Nathan Head - 2/8/2007

Question
Hi,

I am looking to do something similar to a VLOOKUP, only what I am looking up is NOT unique value in that column.

Example of what I need:

NAME     ProductName
Lisa     X1
Mary     X2
Jane     Y1
Bob      Y3
Lisa     Z2
Mary     F3
Bob      G9

I am looking for a formula in where it will list all the products that have been assigned to Lisa, Bob, and so on.

A possible output might be something like this on another tab:

Lisa:
X1
Z2


Mary
X2
F3


Jane:
Y1

Bob:
Y3
G9

---------
Note that text in Col A are not unique and repeat.

Is this possible with a formula in Excel?

Thanks
Lisa Smith

Answer
Lisa:

I have uploaded a sample solution here:
http://home.swbell.net/nate-sus/excel/products.xls

What you are requesting is very useful, but not something easily obtained with Excel; as such, I'll have to do a bit of explaining and then I'll also suggest you review the sample spreadsheet and email me back with any questions:

The sample uses a combination of advanced array formulas and Indexing to provide the desired result.  Each tab contains a set of identical formulas in rows 2 through 25 that look at the name in Cell A1.  The formula then goes to the raw data tab and finds all occurrences of that name and then duplicates the matching rows on the separate tab.

Note #1 - Array formulas have a brace, {}, around them.  In order to change and enter an array formula, you need to press CTRL-SHIFT-ENTER when entering the formula.  If you don't, then changing any of these formulas will likely result in a #VALUE or #REF error.  Fortunately, when copying an array formula, you don't have to do anything special...just copy/paste.

Note #2 - I set up the formulas to check the first 1000 rows of raw data.  The formulas can be modified for more rows if necessary.  Each individual page has 25 rows of formulas...you can simply copy these rows down to get 100 rows...or a 1000 rows, etc.