[vanz123]

Hi,
I have a file with 3 sheets. The layout is similar, but each of the sheets can have different rows / common rows. Pl see attachment..

The 3 sheets are A,B,C and contain cost center information and monthwise numbers. While the columns are the same, the cost centers are not identical across 3 sheets. The CC entries can be same or different. Now, I want to create a 4th sheet with same header information and show for all cost centers, the month-wise calculated values i.e. cells in D will show values at a cost center level (the calc need to be applied even when a cost center is not available in the other sheet; default to 0 when NA)

Cell values in D = cell values in (A+B)-C

Thanks
vani

[JBeaucaire]

It's a bit of a monster, but put this formula in B2 of your new D sheet and copy it down and across the whole chart:

=IF(ISNUMBER(MATCH($A2, A!$A:$A, 0)), INDEX(A!B:B, MATCH($A2, A!$A:$A,0)), 0) +
IF(ISNUMBER(MATCH($A2, B!$A:$A, 0)), INDEX(B!B:B, MATCH($A2, B!$A:$A, 0)), 0) -
IF(ISNUMBER(MATCH($A2, 'C'!$A:$A, 0)),INDEX(A!B:B, MATCH($A2, 'C'!$A:$A, 0)), 0)

It's long, but INDEX/MATCH is one of the most robust functions in Excel, it should stay peppy even on very large datasets.