1

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?

Improve this question
4
  • 1
    This may be useful
    – cybernetic.nomad
    Commented Oct 28, 2018 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
    Commented Oct 28, 2018 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
    Commented Oct 28, 2018 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_
    Commented Oct 30, 2018 at 16:58

1 Answer 1

Reset to default
0

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 : 

=IF(ROW()=2,IFERROR(INDEX($A$2:$A$9,MATCH(F$1,$B$2:$B$9,0)),""),IFERROR(INDEX(OFFSET($A$2:$A$9,MATCH(F1,$A$2:$A$9,0),0),MATCH(F$1,OFFSET($B$2:$B$9,MATCH(F1,$A$2:$A$9,0),0),0)),""))

In G2 :

=IF(F2="","",IFERROR(INDEX($C$2:$C$9,MATCH(F2,$A$2:$A$9,0)),""))

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

Hope it helps.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .