Integrate Indirect into Index and Match

[plateo2000]

Constructed this formula to index column D in sheet FYE2xxx then match to column C in same sheet for answer:

=IFERROR(INDEX('FYE2006'!$D$4:$D$1322,MATCH('3DExp'!X$23,'FYE2006'!$B$4:$B$1322,0)),0)

I am trying to figure out how to use Indirect so I do not have to replace the sheet reference in both the index and match portions manually. In other words, I want to copy the formula down picking up the sheet references from the leftmost column for FYE2006 through FYE2016. Possible?

 

[Domenic]

I guess the leftmost column is Column A, right? Also, you didn't didn't mention the row so I'm going to assume that the sheet references start at '3DExp'!A24...

=IFERROR(INDEX(INDIRECT("'"&'3DExp'!$A24&"'!D4:D1322"),MATCH('3DExp'!X$23,INDIRECT("'"&'3DExp'!$A24&"'!B4:B1322"),0)),0)

Hope this helps!

 

[plateo2000]

I guess the leftmost column is Column A, right? Also, you didn't didn't mention the row so I'm going to assume that the sheet references start at '3DExp'!A24...

=IFERROR(INDEX(INDIRECT("'"&'3DExp'!$A24&"'!D4:D1322"),MATCH('3DExp'!X$23,INDIRECT("'"&'3DExp'!$A24&"'!B4:B1322"),0)),0)

Hope this helps!

Thanks, MVP! Here is a clarification (picking up the correct sheet in the workbook out of the left column which could be titled Fiscal Year, creating the index in that sheet, then coming back to the 3D sheet to pick up the item I want to match from the column head "01-11-421", then back to the Fiscal Year Sheet to find the match in the B Column, then back to the 3D sheet to populate the cell):

[TABLE="width: 296"]
<tbody>[TR]
[TD]Salaries[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Regular[/TD]
[TD]01-11-421[/TD]
[TD]01-21-421[/TD]
[/TR]
[TR]
[TD]FYE2006[/TD]
[TD] $ 90,076 [/TD]
[TD] $ 119,372 [/TD]
[/TR]
[TR]
[TD]FYE2007[/TD]
[TD] $ 84,708 [/TD]
[TD] $ 116,168 [/TD]
[/TR]
[TR]
[TD]FYE2008[/TD]
[TD] $ 88,628 [/TD]
[TD] $ 112,527 [/TD]
[/TR]
[TR]
[TD]FYE2009[/TD]
[TD] $ 66,994 [/TD]
[TD] $ 123,617 [/TD]
[/TR]
[TR]
[TD]FYE2010[/TD]
[TD] $ 89,083 [/TD]
[TD] $ 82,665 [/TD]
[/TR]
[TR]
[TD]FYE2011[/TD]
[TD] $ 91,513 [/TD]
[TD] $ 120,695 [/TD]
[/TR]
[TR]
[TD]FYE2012[/TD]
[TD] $ 90,525 [/TD]
[TD] $ 95,818 [/TD]
[/TR]
[TR]
[TD]FYE2013[/TD]
[TD] $ 93,798 [/TD]
[TD] $ 146,800 [/TD]
[/TR]
[TR]
[TD]FYE2014[/TD]
[TD] $ 32,298 [/TD]
[TD] $ 166,213 [/TD]
[/TR]
[TR]
[TD]FYE2015[/TD]
[TD] $ 32,850 [/TD]
[TD] $ 139,422 [/TD]
[/TR]
[TR]
[TD]FYE2016[/TD]
[TD] $ 32,850 [/TD]
[TD] $ 139,422 [/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

 

[Domenic]

Sorry, but it's unclear to me what it is you're looking for. Can you please clarify?

 

[plateo2000]

Yes Sir: I have a workbook with several sheets, one of which is named 3DEXP and others named for fiscal years (FYE2006, FYE2007,...,FYE2016). Each of the FYE sheets contains financial data for the entire year by line item representing a fund, a department within that fund and the item (01-11-421 for instance mean general fund, admin department, full-time salaries; whereas 01-21-421 means the same thing but a different department, in this case the police department). The worksheet 3DEXP is being used to aggregate data from each fiscal year and the formula I am using does that perfectly:

=IFERROR(INDEX('FYE2006'!$D$4:$D$1322,MATCH('3DExp'!X$23,'FYE2006'!$B$4:$B$1322,0)),0)

In short, the formula is telling Excel to index everything in column D in the worksheet FYE2006, then go to column B and find the line item 01-11-421 or the line item 01-21-421 and return the amount matching the line item number (in this case 90,076 for 01-11 and 119,372 for 01-21). Here is a sample from the worksheet FYE2006, the sheets are similar across all the other years.
[TABLE="width: 809"]
<tbody>[TR]
[TD]SALARIES-EMPLOYEES
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]01-11-421
[/TD]
[TD]SALARIES STAFF
[/TD]
[TD]$90,075.72
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]01-11-422
[/TD]
[TD]SALARIES PART TIME
[/TD]
[TD]$0.00
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]01-11-423
[/TD]
[TD]SALARIES-OVERTIME
[/TD]
[TD]$1,691.22
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]01-11-428
[/TD]
[TD]SALARIES ELECTED
[/TD]
[TD]$5,959.96
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]INSURANCE BENEFITS
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]01-11-451
[/TD]
[TD]HEALTH INSURANCE
[/TD]
[TD]$29,687.85
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]01-11-452
[/TD]
[TD]LIFE INSURANCE
[/TD]
[TD]$2,591.32
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]01-11-453
[/TD]
[TD]UNEMPLOYMENT INSURANCE
[/TD]
[TD]$5,181.86
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]01-11-454
[/TD]
[TD]DENTAL INSURANCE
[/TD]
[TD]$6,232.20
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]01-11-459
[/TD]
[TD]WORKER'S COMPENSATION INSURANCE
[/TD]
[TD]$420.00
[/TD]
[/TR]
</tbody>[/TABLE]

I was hoping to use Indirect to point to the correct worksheet so that when I copy down and across I do not have to go back and manually change the worksheet name for each successive year, because with the current formula that I am using, 'FYE2006'! will not auto change to 'FYE2007'! and so forth.

Here is a sample of aggregated data from the 3DEXP worksheet using the current formula and changing each year manually:
[TABLE="width: 370"]
<tbody>[TR]
[TD]Salaries
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Regular
[/TD]
[TD]01-11-421
[/TD]
[TD]01-21-421
[/TD]
[/TR]
[TR]
[TD]FYE2006
[/TD]
[TD]$ 90,076
[/TD]
[TD]$ 119,372
[/TD]
[/TR]
[TR]
[TD]FYE2007
[/TD]
[TD]$ 84,708
[/TD]
[TD]$ 116,168
[/TD]
[/TR]
[TR]
[TD]FYE2008
[/TD]
[TD]$ 88,628
[/TD]
[TD]$ 112,527
[/TD]
[/TR]
[TR]
[TD]FYE2009
[/TD]
[TD]$ 66,994
[/TD]
[TD]$ 123,617
[/TD]
[/TR]
[TR]
[TD]FYE2010
[/TD]
[TD]$ 89,083
[/TD]
[TD]$ 82,665
[/TD]
[/TR]
[TR]
[TD]FYE2011
[/TD]
[TD]$ 91,513
[/TD]
[TD]$ 120,695
[/TD]
[/TR]
[TR]
[TD]FYE2012
[/TD]
[TD]$ 90,525
[/TD]
[TD]$ 95,818
[/TD]
[/TR]
[TR]
[TD]FYE2013
[/TD]
[TD]$ 93,798
[/TD]
[TD]$ 146,800
[/TD]
[/TR]
[TR]
[TD]FYE2014
[/TD]
[TD]$ 32,298
[/TD]
[TD]$ 166,213
[/TD]
[/TR]
[TR]
[TD]FYE2015
[/TD]
[TD]$ 32,850
[/TD]
[TD]$ 139,422
[/TD]
[/TR]
[TR]
[TD]FYE2016
[/TD]
[TD]$ 32,850
[/TD]
[TD]$ 139,422
[/TD]
[/TR]
</tbody>[/TABLE]
Here is the formula changed to allow indexing and matching on the FYE2007 sheet: =IFERROR(INDEX('FYE2007'!$D$4:$D$1322,MATCH('3DExp'!X$23,'FYE2007'!$B$4:$B$1322,0)),0).

Everything is the same except that I changed the year from FYE2006 to FYE2007 and would have to do the same for each of the successive sheets.

 

[Jonmo1]

That's what Dominc's formula will do
You just have to adjust the cell that contains the name of the sheet to reference, highlighted in red below.

I guess the leftmost column is Column A, right? Also, you didn't didn't mention the row so I'm going to assume that the sheet references start at '3DExp'!A24...

=IFERROR(INDEX(INDIRECT("'"&'3DExp'!$A24&"'!D4:D1322"),MATCH('3DExp'!X$23,INDIRECT("'"&'3DExp'!$A24&"'!B4:B1322"),0)),0)

Hope this helps!

 

[Jonmo1]

Though I might suggest VLOOKUP, so there will be only 1 indirect call.

=VLOOKUP('3DExp'!X$23,INDIRECT("'"&'3DExp'!$A24&"'!B4:D1322"),,3,FALSE)

 

[plateo2000]

Thank you all! My apologies to Mr. Domenic. I managed to mess up his perfectly good formula by somehow, inadvertently, eliminating a double quote. Good grief. You guys are the greatest. Thanks again.