Combining indirect, match and index function in excel

[Lor365]

Hi
I have a workbook with multiple tabs 'ABC', 'DEF', etc containing trials and in one tab called 'Check' i have in (Fig 1.) column 'A' the name of all these sheets down along under each other. Every time I use this workbook these tabs will include different information but the format will always be the same. In the check tab I want in column D to bring in the following figures...'Total Income line........, (always line 116) under different columns i.e. Basis/Sleeve: which will change continuously and can expand out to column AZ depending.
Fig 1
View attachment 10064

So in short I want the formula to e.g. look at cell A3 (indirect) to tab ABC and match basis/Sleeve: F and go down as far as the total Income line and return the value.
Please note that the tabs with info are not in a table format and contain gaps between rows and columns

I would really appreciate if someone could help me on this

 

[Saba Sabaratnam]

Hi

I have set up a worksheet layout as presented below.

Based on my layout of the tab, I have a formula in Cell E2 and copied it down.

=INDEX(INDIRECT(B2),115,MATCH(D2,INDIRECT(C2),0))



Kind regards

Saba