Looking up Excel values based on sheets and rows

Question

I was wondering if it's possible to look up values based on sheet names and certain columns inside those sheets? I was trying to wrap my head around that idea, but couldn't actually find a solution, so I opted out by using this formula to match my sheet name with the value I need it to return:

=IFERROR(INDIRECT("'"&$B3&"'!BX35");"")

I need information to look like this:

MAIN SHEET

Sheet2

Sheet3

Sheet4

There could also be 100s of sheets. The rows where the information is, is always kept in the same rows, for this example from 1:4, as for columns, sometimes they differ, is it possible to return the information from the right most side, based on months?

Right most side is needed, since sometimes the information might come from earlier years and there might be month doubles (f.e. 2023 year with months from 1 to 12 and 2024 year with months from 1 to 6, I'd need to return the information for the year 2024 and months from 1 to 6)

Answer 1

Replicated your data and this worked for me using INDIRECT:

=INDEX(INDIRECT("'"&$B3&"'!4:4");1;MATCH(C$2;INDIRECT("'"&$B3&"'!2:2");0))

Notice INDIRECT is a volatile function and it may really overcharge your workbook if you use it a lot.

Answer 2

You could use HLOOKUP to find the cell corresponding to the required column, and INDIRECT to specify the required range to search.

Example formula in cell F3 of your MAIN TABLE:

=HLOOKUP(F$2, INDIRECT($B3&"!2:4"), 3, TRUE)

Answer 3

Your sample data did not include any examples of sheets where there are more than 12 months of data, so I think the other solutions did not take this into account. I added a Sheet5 to test what happens with more than 12 months of data. This should work no matter how many months are in your sheets:

The formula in C3 is =LET(CURRENTSHEET,INDIRECT($B3&"!1:4"), TAKE(FILTER(CURRENTSHEET,CHOOSEROWS(CURRENTSHEET,2)=C$2),-1,-1))

LET to define CURRENTSHEET, which is all the data on the sheet you need to use

FILTER and CHOOSEROWS to only use the data that matches the month you want to use

TAKE to only keep the right-most matching month, and the 4th row of data

Answer 4

Adding another answer, because although you didn't tag Power Query, that's what I would use given the option. It avoids the use of INDIRECT and organises the data in a better way.

First create a query to list all the sheets in the workbook, and filter out any sheets that don't contain your data

Then create a function to clean the data on each sheet, only keeping the last 12 months of data from each sheet

Then create a new query that references the first query, and use the function on each sheet. Load that to a table, and now you can use SUMIFS or Pivot Tables instead of INDIRECT.