Lookup / Indirect function to lookup information from various tabs/worksheets

First post, please help if possible.

In the attached file, I need a formula in B2 that can:-
1: Find a tab/worksheet that is the same name as A2.
2: On that tab/worksheet, find the heading that is the same as B1
3: Return the value that is in row 12 of that tab
4: Copy the formula for the above columns and rows.

I have left only 2 tabs in to make the file smaller. Happy to provide further information if rquired.


Thanks,
Raj

It would help if you provided some expected output?
What info do you want from the sheet?

Assuming you want to pull the data from row 2 (Units)...
B2=IFERROR(INDEX(INDIRECT("'"&$A2&"'!C2:AG2"),MATCH(B$1,INDIRECT("'"&$A2&"'!C1:AG1"),0)),"")

copied down and across as needed.

Great stuff, happy to help

Sure.

This is really just a regular INDEX/MATCH, with the added complexity of using INDIRECT to ID the sheet you want to refer to...
=IFERROR(INDEX(INDIRECT("'"&$A2&"'!C2:AG2"),MATCH(B$1,INDIRECT("'"&$A2&"'!C1:AG1"),0)),"")
Broken down, it works like this, without the INDIRECT or IFERROR part...
=INDEX('21 Genge St'!C2:AG2,MATCH(B$1,'21 Genge St'!C1:AG1,0))
(looks a lot simpler there, doesnt it? - let me know if you need that part explained?)

OK but because you want the formula to find the sheet name, instead of hard coding it, we need to use the INDIRECT function. The same principal to bot INDIRECTS there, so I will explain the 1st 1.
=IFERROR(INDEX(INDIRECT("'"&$A2&"'!C2:AG2"),MATCH(B$1,INDIRECT("'"&$A2&"'!C1:AG1"),0)),"")

INDIRECT("'"&$A2&"'!C2:AG2")
If you look at the sheet reference in the simplified version, you will see it reads...
'21 Genge St'!C2:AG2

Note that there is ' surrounding the sheet name. Because the cell that contains the sheet name text does not contain those, we need to add them...
INDIRECT("'"&$A2&"'!C2:AG2")
However, because they are text, they need to be wrapped inside ""

The & combines different text strings, so what we then have is...
"'" and (the text in) $A2 and"'(...the cell range goes here, as another text string)!C2:AG2")

Because that cell range is no longer an actual cell range, but has been converted to text, we can do away with the absoluting

Did that make sense? If not, please shout again and I will try again

(and thanks for the feedback)

Happy to help and thanks for the feedback