Index Match (or something else?) with multiple criteria (didn't see a solution in other threads)

[eagerexceler]

Hi Y'all - I can't solve this formula I'm trying to come up with. Sorry, I'm not able to download XL2BB on my work computer.

I have two tabs, and I'm trying to pull data from my Data tab into my Summary tab based on multiple criteria. In the summary tab I'm trying to pull amounts in column F based on matching the local from cell B2, the Code from column A, and the period number in column E. My data set includes hundreds of other localities and codes, so this is just a snippet. So basically I'm trying to conditionally grab cell D3 from the Data tab and put it in cell F4 of the Summary tab. I've tried index match with multiple conditions, but I can't get it to work at all.

Thank you in advance for any help.

Summary tab:

LOCAL	Houston
Code	Description	Lab	Name	Period	Amount
1A	ADM 1	Er1	Ntech	1​
1A	ADM 1	Er1	Ntech	2​
1A	ADM 1	Er1	Ntech	3​
2A	ADM 2	Er1	Ntech	1​
2A	ADM 2	Er1	Ntech	2​
2A	ADM 2	Er1	Ntech	3​

Data tab:

			1A	1A	1A	2A	2A	2A
Contract	Local	Local Name	1	2	3	1	2	3
412​	8​	HOUSTON	17.28​	19.5​	25.4​	50.45​	58.6​	69.12​
412​	9​	OTHER	9.25​	12.54​	18.84​	38.21​	46.75​	51.34​

 

[Alex Blakenburg]

See if this works in F4

=INDEX(Data!$C$3:$I$4,MATCH($B$1,Data!$C$3:$C$4,0),MATCH($A4,Data!$C$1:$I$1,0)+$E4-1)

Your pictures are different to your data provided, if you need to pad the summary data out to 5 characters eg 1A need to look up 0001A then try this:

=INDEX(Data!$C$3:$I$4,MATCH($B$1,Data!$C$3:$C$4,0),MATCH(REPT("0",5-LEN($A4))&A4,Data!$C$1:$I$1,0)+$E4-1)

 

[eagerexceler]

Hey Alex, both of your solutions work. Thank you for the help - you taught me some new things so I appreciate that, too.