Excel Lookups

[jadeandandrew]

Hi all,

I am trying to create a lookup sheet which can be used for other
sheets. I have a list of shops and a list of products. I want to be
able to lookup all of the shops that supply a specified product and
(on a seperate lookup) all of the products that are supplied by a
specified shop. The following comma delimited example is what I'm
trying at the moment.

Tesco, Apples
Tesco, Bananas
Sainsburys, Oranges
Waitrose, Grapes
Waitrose, Peaches
Waitrose, Pears

I can use a HLookup on this either way (products to shops or shops to
products), but I can only return one result, not the whole range. Here
is another idea..

Tesco, Apples, Bananas
Sainsburys, Oranges
Waitrose, Grapes, Peaches, Pears

I can use a HLookup again, and return all of the products a specific
shop supplies, however I can't return all the shops that supply a
specified product.

I either need one sheet that I can lookup boths ways on, or two sheets
with one of them dynamically updating from the other. It would be
fairly straight forward using a macro, however on this occassion this
is not an option.

Any help would be much appreciated!! Thanks!

 

[Tom Ogilvy]

If you want to send me a small sample workbook, I will show you how to do it.
Show what you want the output to look like for each output table.
(e-mail address removed)

Another alternative is to use a pivot table.
Look under pivot tables:
Debra Dalgleish
http://www.contextures.com/tiptech.html