Excel: Check for specific value in a matching row

Question

I want to check which products (listed in rows) are produced by which company (listed in column headings) in order to fill a table like this

           Company A  Company B Company C
Product 1 
Product 2
Product 3

The raw data is stored in a separate Excel worksheet that is designed like this:

Company A Product 3 Product 5 Product 7 Product 10 Product 11
Company B Product 1 Product 7
Company C Product 1 Product 8 Product 9

So I would first need to command Excel to select the right row (=company) and then check whether it contains the product I am looking for. I do not think VLOOKUP in its basic form can help here, since it can only return the value of a specific column. I know there must be an easy solution for this, please let me know if you have an idea! Thank you so much for your help!

Answer 1

Couple suggestions. First one is to put the data in a more usable format and then go from there in this link you will find a macro that changes the data to the format

Company  Product
A        Product 3
A        Product 5
A        Product 7
B        Product 1
B        Product 7
etc

Then you could use a pivot table.

Otherwise you could do it with a function. This function is in cells B9:D19 in the screenshot below.

=IFERROR(--MATCH($A9,OFFSET($1:$1,MATCH(B$8,$A$2:$A$4,0),0),0)>0,"")

My layout is shown below you will need to change your ranges to be similar. I would be happy to provide further information about the function if you require it.

Gordon

Answer 2

This worked for me:

=IFERROR(MATCH($A15,OFFSET($A$1,MATCH(B$14,$A$1:$A$6,0)-1,0,1,255),0),"")

Where the cell references were:

=IFERROR(
  MATCH([Product 1 in report],
        OFFSET([Company A in raw table],
               MATCH([Company A in report],
                     [column holding company names],
                     0)-1,
               0,
               1,
               [max width of raw table]),
        0),
  "")

You could add in:

=IFERROR(IF(MATCH($A15,OFFSET($A$1,MATCH(B$14,$A$1:$A$6,0)-1,0,1,255),0)>0,"X"),"")

to make it put X's where there were matches instead of numbers.