Indirect, Index, Match, Table[#Data]?? [ Solved ]

[BrianB2026]

I could probably Brute Force my way through this, but I'm hoping to get a more efficient, elegant solution from someone more practiced than I am.

All the sheets are contained in one workbook.
Sheet 1 is the target sheet.
Sheet VariableName is the lookup sheet.
Sheet 1, $F1:$Q1 contain the Month & Year, "Jan 2026", with the actual value set as 1/1/2026 thru "Dec 2026 (12/1/2026).
Sheet 1, A:A contain the Name of the lookup sheet - "VariableName"
Sheet VarialbeName contains a Table (Table1, Table range A19:H1000 and all of the VariableName sheets will be formatted the same).
Sheet VariableName, Table1, Column A contains Dates formatted MM/DD/YYYY.
Sheet VaraibleName, Table1, Column C contains the values I want to return.

For Instance: I want to fill Sheet1 $F50:$Q50 with values from the sheet 'VariableName', $C$22:$C$1000
Where:
The sheet name VariableName matches the value in Sheet1, A50
To get the row for Column C in VariableSheetName, Table1, I need to match the month from Sheet1, F$1:Q$1 to the month in the sheet VariableName, Table1, $A22:$A1000.

So on my target sheet, Sheet1, and target cell F50, I want the formula to look at the name in Sheet1, A50, go to the Sheet that matches that name in A50, find the row number by matching the Month & Year in Sheet1, F$1 with Table1, A22:A1000 then return the value from Table1, Column C and that corresponding row#.

Of course, and as always, any input is greatly appreciated.

Mod edit:

Multiple Index/Match (I think)

I could probably Brute Force my way through this, but I'm hoping to get a more efficient, elegant solution from someone more practiced than I am. All the sheets are contained in one workbook. Sheet 1 is the target sheet. Sheet VariableName is the lookup sheet. Sheet 1, $F1:$Q1 contain the Month...

www.mrexcel.com

Indirect Index Match and Table Data# ?

I could probably Brute Force my way through this, but I'm hoping to get a more efficient, elegant solution from someone more practiced than I am. All the sheets are contained in one workbook. Sheet 1 is the target sheet. Sheet VariableName is the lookup sheet. Sheet 1, $F1:$Q1 contain the...

www.excelforum.com

 

[vletm]

You seems to have an Excel-file.
Without a sample Excel-file as You have other would have a challenge to get clear image - what do You have there?
... or other should try to guess and do something ... and keep on guessing.
Your should able to send a sample Excel-file - which has some sample data and sheets as You have.

 

[BrianB2026]

Thank you, yes. This was/is an Excel file.
Thanks for the response.
I did figure it out. Turns out, I was simply over-thinking it.
I did however, add a helper column to the Source Sheet to help me figure it out. That became column B and the resulting formula became pretty simple actually:
=INDEX(INDIRECT("'"&A59&"'!D:D"),MATCH(TEXT(F$1,"mmyy"),INDIRECT("'"&A59&"'!B:B"),0))

I'm not sure how to delete the post, or mark it solved however.

 

[vletm]

BrianB2026​

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.