I have a table with some data that is similar to the table below:

enter image description here

I would like to display a table on the right of this table to show each colour product in its own column:

enter image description here

The worksheet would therefore look something like this:

enter image description here

  • When update the table on the left, i would like the table on the right to update automatically (including adding and removing rows).

After googling around, i found a few examples but none of them seem to do what i would like to do:

  • The data can be filtered but i am not sure if filtering can be done with multiple tables.

  • I tried a Pivot table but could not get it to show anything other than totals. Maybe i did something wrong but the only view i could get was totals and sums of the values - i could not create the second table using a pivot table.

  • Is it possible to do this with VLookup but not have blank entries where there is no match?

  • 1
    This may be useful Oct 28 '18 at 11:52
  • Can you elaborate on what you mean by whether or not "filtering can be done with multiple tables?" Might give some more insight into best solution for you. One approach, and I realize it's not what you are asking for because it remains fairly manual, would be to copy the data into three new tables and then filter each of those tables (Red, blue and green).
    – Jonathan
    Oct 28 '18 at 20:34
  • I think that a solution, using what used to be called PowerQuery, is at the following link. Not sure if this functionality is available in all versions of Excel 2016. Link: link
    – Jonathan
    Oct 28 '18 at 20:45
  • "•Is it possible to do this with VLookup but not have blank entries where there is no match?" -> =iferror(vlookup( ... ),"")
    – p._phidot_
    Oct 30 '18 at 16:58

Assuming the Product:Colour:Price table is located at A1:C9, and the Red:Price:Blue:Price:Green:Price table is located at F1:K9 :

In F2 put :


In G2 :


then drag both until K9. Please if it works/stuck/error.. ( :

Hope it helps.

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.