Search code examples
excelindexingexcel-2010worksheet-functioncountif

Using INDEX partially to populate a matrix from a list

My Excel spreadsheet has two columns: Product IDs and the Catalog View they have. Products may have several different Catalog Views.

How may I use INDEX to bring the one record of the Product ID and then all the associated Catalogs laid out to the right of the Product ID?

Link to the example here: https://drive.google.com/file/d/0ByXE6KdAx3STUjlQODZlS25zTlU/view?usp=sharing and screenshot below:

enter image description here

Solution

  • Assuming TOP2 is in A9, please try in F2: 

    =IF(COUNTIF($A:$A,$E2)>=1*RIGHT(F$1),INDEX($B:$B,MATCH($E2,$A:$A,0)+1*RIGHT(F$1)-1),"")

    copied across and down to suit.