Index and Match - Multiple Vertical Headers (Colmn) and Multiple Horizontal Headers (Rows)

Hi Everyone,

I have a table with multiple vertical and horizontal headers.

The first horizontal header has shapes (Triangle, Quadrilateral) which are merged. The secondary horizontal header has sub categories (Equal sides, Not equal sides).

The first vertical header has colors (Yellow, Blue) which are merged. The secondary vertical header has sub categories (Light, Medium, Dark).

The table is then populated with digits in each cell.

Excel sheet is attached.


What I'm trying to accomplish is look up based on the shapes and their sub category as well as the color and the shade. Cells B11-B14 hold the input, and B16 would have the formula (combo of Hlookup,Index,Match,Vlook up,etc) with the result.

For example -

• The result for Shape=Triangle, Shape Sub=Equal sides, Color=Blue, Shade=Medium would be 5.
• The result for Shape=Quadrilateral, Shape Sub= Not Equal sides, Color = Yellow, Shade=Dark would be 27.

Try this...
=INDEX($C$3:$F$8,MATCH(B13,A3:A8,0)+MATCH(B14,B3:B5,0)-1,MATCH(B11,C1:F1,0)+MATCH(B12,$C$2:$D$2,0)-1)

Originally Posted by jzee

Thank you!

You simplified it so much.

Can you explain this part for me please?
MATCH(B13,A3:A8,0)+MATCH(B14,B3:B5,0)-1

Lets use the Blue/Medium in your example

The 1st MATCH determines which color to pick (row 4), then because the shade is the same for both colors, I just used the 2nd MATCH to ID the shade (row 2 - and it doesnt matter which color I use for the shade, they are the same) - then added that value (2) to the value selected from the 1st MATCH (4). However, because the 2nd match would add 1 too many rows (I actually need 4 + 1, not 4 + 2) I needed to subtract 1.

Hopefully that makes sense? If not, should and I will try explaining again.

Originally Posted by jzee

Thank you. I'll need to review the formula.

Unfortunately, the actual table I have contains many more categories (7 Vertical, 7 Diagonal) and so the merging provides a better representation.

Then I suggest you provide a more representative sample file for us to play with )