Multiple Look up returns header values

[maggsy]

I have a table that has suppliers identified on the 1st row.
The cells in the table specify the goods the suppliers can provide.
i.e Column 1 - Supplier A, Bread, Butter, Crisps, Cheese
Column 2 - Supplier B, Crisps
Column 3 - Supplier C, Cheese, Fruit, Bread

I have created a drop down list of all the products. What I want to be able to do is for someone to select a product and for a list of all the suppliers who can provide this product to be returned.

I have tried playing around with lookups but can't get anything to work due to the random placement of good type within the table.

The data has been exported from another source and is of a large scale so it is not practical to reformat into a different order.

 

[rdw72777]

Okay, this might be tricky to explain, but here goes

1. Setup your data with vendor names in row 1, and the products they sell in rows 2-10. i think you've already done this, but just want to be sure.

2. In row 12 of your data, paste this formula in cell A12, then copy and paste A12 into cells B12 and C12. Change $F$2 to whatever cell has the product value from your dropdown list

=COUNTIF($A$1:A10,$F$2)

3. In row 13, paste the following formula in Cell A13 and then copy paste Cell A13 into cells B13 and C13

=SUM($A$12:A12)

4. In row 14, copy this formula to cell A14, then copy cell a14 and past into cells B14 and C14

=IF(A12>0,A1,"")

5. In cell F3, paste the following formula
=SUM(A12:C12)

6. Type "'<--Number of vendors selling this product" in cell G3

7. In cell E4, paste the following formula. Then copy and paste cell E4 down for 10 rows.

=IF($F$3>ROW()-4,"Vendor "&ROW()-3,)

8. In cell F4, paste the following formula. Then copy and past cell e4 down for 10 rows

=IF($E4="","",HLOOKUP(ROW()-3,$A$13:$C$14,2,0))


This worked for me. Let me know how it works for you.