Index Match with Multiple Top Level Entities

For this formula to work, you do need to add the word TOTAL below the data on the Raw Data sheet after you drop the data in, just type TOTAL in column A below the data.

Put this formula in B3, then copy across all the white cells in your table:

=IFERROR(INDEX(INDEX('Raw Data'!$B:$B,MATCH(B$1,'Raw Data'!$A:$A,0)):INDEX('Raw Data'!$B:$B,MATCH(C$1,'Raw Data'!$A:$A,0)-1),MATCH($A3,INDEX('Raw Data'!$A:$A,MATCH(B$1,'Raw Data'!$A:$A,0)):INDEX('Raw Data'!$A:$A,MATCH(C$1,'Raw Data'!$A:$A,0)-1),0)), "")

A unique RANGE of cells is being evaluated using the HEADERS in row 1 of the table where you're putting the formula.

SO B3 is creating a range that indexes all the values between the Allan Corp and Beta Corp, look in the formula and you'll spot references to cells B1 and C1, that's the formula spotting those cells in the Raw Data table and using ONLY those cells in between.

When you copy that formula to the right those references change to use the headers at the top of the current row and the row to the right. So the last column it won't work because the header in the column to the right is "TOTAL" and that was missing from the data. Simply typing it in makes it work for all columns including the last one, poor man's fix.


If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.