returning value from intersection based on criteria

[1mrbrooks]

I am trying to return a value in an excel table (Archive_Table) for the previous month into another table (Table_Cost). I have a host of numbers from various tables that I archive after completing the report in the Archive_Table, which creates a new column with the header being the month ending date of the report. In Table_Cost, the column headed Previous Forecast must be populated with the data that is one month behind the current report date (named MonthEndingDate). Since new columns will be added each month, the formula will need to match data with date headers in the archive table with EOMONTH(MonthEndingDate,-1) and Description & Current Forecast (header) in Table_Cost with Data_Type & Category_Craft in the Archive_Table. Example, I want to return the previous month's Engineering Forecast for the current report that is 6/30/2016... the data returned must be the value of the intersection of column 5/31/2016, where the value of the rows under columns Data Type & Category_Craft equals "Current Forecast" & "Engineering" ...

Sample Archive_Table Data:

Data_Type	Craft_Category	4/30/2016	5/31/2016
JTD Cost	Engineering	5,000	10,000
JTD Cost	Direct Materials	1,000	3,000
Current Forecast	Engineering	50,000	50,000
Current Forecast	Direct Materials	100,000	100,000
Budget	Engineering	50,000	50,000

<tbody>
</tbody>

Sample Table_Cost Data current month ending 6/30/2016:

Description	As Sold	Budget	Committed Cost	Period Cost	JTD Cost	Current Forecast	Previous Forecast
Engineering	45,000	50,000	50,000	5,000	12,000	55,000	=some lookup formula
Direct Material	95,000	100,000	60,000	10,000	35,000	100,000	=some lookup formula
Direct Labor	40,000	40,000	20,000	5,000	10,000	40,000	=some lookup formula
Indirect Labor	25,000	25,000	14,000	3,000	9,000	25,000	=some lookup formula

<tbody>
</tbody>

For Direct Material above the Previous forecast should return 100,000 and for Engineering it should return 50,000. There are 12 rows on Table_Cost and 240 rows in the Archive_Table that have various key items that are archived each month. Both are tables and not ranges and the first row is the header. The archive will add columns each month and the cost table will remain the same size.

I can match the column number... =MATCH(TEXT(EOMONTH(MonthEndingDate,-1),"m/dd/yyyy"),Archive_Table[#Headers],0)... returns col 4 in my actual table and I can match the row {=MATCH(Table_Cost[[#Headers],[Current Forecast]]&R_Cost!A5,Archive_Table[Data_Type]&Archive_Table[Category_Craft],0)}... returns row 45 in my actual table, but I can't seem to put it together and return the value at that intersection in the Previous Forecast column. Any help would be appreciated.

 

[oldbrewer]

have a look at offset combined with match

Current Forecast

Direct Materials

<tbody>
</tbody>

and consider concatenating eg the above 2 to take ambiguities out of the search

 

[oldbrewer]

DTCC	Data_Type	Craft_Category	4/30/2016	5/31/2016
JTD CostEngineering	JTD Cost	Engineering	5,000	10,000
JTD CostDirect Materials	JTD Cost	Direct Materials	1,000	3,000
Current ForecastEngineering	Current Forecast	Engineering	50,000	50,000
Current ForecastDirect Materials	Current Forecast	Direct Materials	100,000	100,099
BudgetEngineering	Budget	Engineering	50,000	50,000
	Sample Table_Cost Data current month ending	6/30/2016	prev period	5/31/2016
	Description	As Sold	Budget	Committed Cost	Period Cost	JTD Cost	Current Forecast	Previous Forecast
	Engineering	45,000	50,000	50,000	5,000	12,000	55,000
	Direct Materials	95,000	100,000	60,000	10,000	35,000	100,000	100099
	Direct Labor	40,000	40,000	20,000	5,000	10,000	40,000
	Indirect Labor	25,000	25,000	14,000	3,000	9,000	25,000
				formula giving 100099
		=OFFSET($A$3,MATCH($H$22&B24,$A$4:$A$8,0),MATCH($G$20,$D$3:$Z$3,0)+2)
		note I have put current and prev month in their own cells in lower table
		should be possible to automate prev period date maybe with a lookup table

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[1mrbrooks]

Thanks. Yes, I have tried that very solution. I have the concatenated column in my actual table. I always got an error #N/A. I even got rid of the table format to see if that was the issue. I copied and pasted the data from my tables into another sheet and wrote the same formula (with different addresses) and used a date field like you have above. The second match() kept giving me an error until I retyped the date in my table for some reason and I had to subtract 1 from the first match and add 3 to the second match... not really sure why, but it works. I have yet to try it out on the actual tables. Thanks for the solution, I will try it out later today in the actual file and see what happens. Thanks again!!