Index Match Between Tables

Hi all,

I'm currently working between a couple of two tables and finding it fairly tedious copying/pasting between them.

I've been messing around with index/match formulae, but can't quite get them to stick.

I'm essentially trying to populate one table with data contained in the another (see attached workbook).

Any guidance would be greatly appreciated!

D.

Originally Posted by FlameRetired

There are two impediments to doing what you state.

1. Merged cells (Table 2) cause havoc for formulas. Repeating the row headers is preferable.
2. The offset row sub headers in the second table are not consistent with those in the first table. While relatively minor, those can make formulas more complicated. Replacing the merged cells with repeating headers will help. A helper column can fulfill the role of repeating headers, but as a general rule one should avoid merged cells. They are the devil's spawn.

What method would you prefer? Repeating headers ... helper column.

Haha, thanks for the quick response!

I definitely plan on unmerging the cells, and my instinct would say that repeating headers would be easier, the only issue is that the green table is a copy of a pivot table I'm currently using, so repeating headers may or may not be an issue, unless I create another header above the table that can/does repeat. Otherwise, I would go with the helper column.

Thanks again!

Originally Posted by FlameRetired

Good deal!

I guessed correctly.

In the attached find (now un hidden) a helper column A. This formula repeats the merged row headers.

Formula:

=IF(B37="",A36,B37)

I figured out the M F headers and their role in the output columns. In D37:H41

Formula:

=INDEX($C$3:$O$30,MATCH($A37,$B$3:$B$30,0)-1+MATCH($C37,$B$3:$B$30,0),

MATCH(LOOKUP("zzzzz",$D$35:D$35),$C$1:$O$1,0)-1+MATCH(D$36,$C$2:$O$2,0))

With that range selected copy and paste into cells D43, D49, D55, J37, J43, J49 and J55.

For the subtotals by M and F in I37:I41

Formula:

=INDEX($C$3:$O$30,MATCH($A37,$B$3:$B$30,0)-1+MATCH($C37,$B$3:$B$30,0),

MATCH(2,INDEX(1/(LOOKUP("zzzzz",$D$35:D$35)=$C$1:$O$1),0),1))

Then copy that range and paste into the remaining relative cells columns I and O. The formula subtotals for each A, B, C ... E and gender subtotals are self explanatory. Again copy the first range and paste into relevant cells.

I assume you know how to hide column A if desired.

Wow, this worked phenomenally. Can't thank you enough!

I've hidden the helper column and it definitely looks less crowded. Thank you again for the amazing guidance.