1

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!

0

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

enter image description here

| improve this answer | |
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.