Trouble with INDEX/MATCH for Multiple Criteria

[Shawn09]

I'm having an issue with my index/match formula here and I've messed around with it so much now my brain is just tired of trying to fix it so I'm hoping someone here with fresh eyes can be of assistance to me. My data is is B4:R21 and the formulas are below in the pink cells. The yellow cells are changeable based on what I'm trying to look up. I want to be able to change Gross to Bakery, Dining etc, or change the date and those match up with the correct store number. Hope that makes sense.

Thanks in advance!

Sample.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1
2
3
4		Description	Store	2018-01-31	2018-02-28	2018-03-31	2018-04-30	2018-05-31	2018-06-30	2018-07-31	2018-08-31	2018-09-30	2018-10-31	2018-11-30	2018-12-31	2019-01-31	2019-02-28	2019-03-31
5		GROSS SALES	3368	$ 168,284	$ 158,639	$ 185,883	$ 176,572	$ 180,594	$ 164,151	$ 168,702	$ 169,875	$ 157,472	$ 173,086	$ 163,594	$ 167,655	$ 150,295	$ 150,309	$ 180,390
6		BAKERY SALES	3368	$ 22,526	$ 18,357	$ 22,589	$ 23,453	$ 21,731	$ 19,559	$ 19,212	$ 20,737	$ 20,423	$ 22,906	$ 20,542	$ 24,941	$ 24,462	$ 20,118	$ 23,092
7		DINING SALES	3368	$ 145,758	$ 140,282	$ 163,294	$ 153,119	$ 158,863	$ 144,577	$ 149,489	$ 149,139	$ 137,048	$ 150,179	$ 143,047	$ 142,695	$ 125,833	$ 130,191	$ 157,297
8		MERCHANDISE SALES	3368	$ -	$ -	$ -	$ -	$ -	$ 15	$ -	$ -	$ 1	$ 2	$ 4	$ 20	$ -	$ -	$ 0
9		DELIVERY CHARGE	3368	$ 3,686	$ 3,455	$ 3,823	$ 2,869	$ 2,997	$ 2,642	$ 2,581	$ 2,750	$ 3,082	$ 3,151	$ 3,005	$ 3,017	$ 3,225	$ 3,264	$ 3,738
10
11		GROSS SALES	3371	$ 197,478	$ 185,618	$ 207,906	$ 192,209	$ 215,522	$ 184,148	$ 194,765	$ 207,697	$ 197,211	$ 217,090	$ 195,062	$ 202,188	$ 196,866	$ 187,696	$ 207,082
12		BAKERY SALES	3371	$ 29,448	$ 25,865	$ 30,284	$ 28,258	$ 29,592	$ 25,403	$ 28,615	$ 28,431	$ 28,961	$ 33,509	$ 29,249	$ 34,434	$ 31,913	$ 28,115	$ 31,134
13		DINING SALES	3371	$ 168,030	$ 159,753	$ 177,619	$ 163,947	$ 185,923	$ 158,744	$ 166,150	$ 179,263	$ 168,250	$ 183,579	$ 165,809	$ 167,742	$ 164,949	$ 159,579	$ 175,949
14		MERCHANDISE SALES	3371	$ -	$ -	$ 3	$ 4	$ 7	$ 1	$ -	$ 3	$ 0	$ 1	$ 3	$ 11	$ 4	$ 2	$ 0
15		DELIVERY CHARGE	3371	$ 1,616	$ 1,740	$ 1,851	$ 1,849	$ 1,933	$ 1,288	$ 1,612	$ 1,598	$ 2,885	$ 4,031	$ 4,345	$ 4,125	$ 4,885	$ 5,111	$ 5,795
16
17		GROSS SALES	3374	$ 213,209	$ 191,474	$ 213,059	$ 188,813	$ 205,736	$ 182,490	$ 177,724	$ 198,858	$ 173,920	$ 193,714	$ 189,858	$ 201,737	$ 202,317	$ 184,328	$ 202,447
18		BAKERY SALES	3374	$ 23,800	$ 19,807	$ 23,254	$ 20,887	$ 21,150	$ 18,988	$ 17,767	$ 20,085	$ 18,641	$ 19,175	$ 21,962	$ 26,716	$ 23,016	$ 20,945	$ 24,169
19		DINING SALES	3374	$ 189,409	$ 171,665	$ 189,805	$ 167,925	$ 184,586	$ 163,502	$ 159,955	$ 178,773	$ 155,279	$ 174,538	$ 167,896	$ 175,021	$ 179,302	$ 163,383	$ 178,277
20		MERCHANDISE SALES	3374	$ 1	$ 2	$ 0	$ -	$ -	$ 0	$ 2	$ 0	$ -	$ 0	$ -	$ -	$ -	$ -	$ -
21		DELIVERY CHARGE	3374	$ 4,374	$ 3,930	$ 4,416	$ 4,302	$ 5,411	$ 4,581	$ 4,008	$ 4,880	$ 3,919	$ 4,563	$ 4,256	$ 4,441	$ 5,005	$ 4,922	$ 5,058
22
23
24					GROSS SALES
25					2018-07-31
26							Correct Answer
27				3368	$ 197,478		168701.92
28				3371	$ 194,765		194764.66
29				3374	$ 196,866		177723.5
Sheet1

Cell Formulas
Range	Formula
D5:R5,D17:R17,D11:R11	D5	=SUM(D6:D8)
E27:E29	E27	=INDEX($D$5:$R$21,MATCH($E$25,$D$4:$R$4,0),MATCH($E$24&"|"&$D27,INDEX($B$5:$B$21&"|"&$C$5:$C$21,0),0))

 

[Fluff]

What version of Excel do you have?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Shawn09]

Apologies... been a minute since I've posted... I have updated my profile settings as well but i'm using Office 365 on a Windows machine.

 

[Fluff]

Thanks for that.
You have the two match functions the wrong way round, it should be rows then columns like

=INDEX($D$5:$R$21,MATCH($E$24&"|"&$D27,INDEX($B$5:$B$21&"|"&$C$5:$C$21,0),0),MATCH($E$25,$D$4:$R$4,0))

 

[Fluff]

Another option is to use the filter function

=FILTER(FILTER($D$5:$R$21,($B$5:$B$21=$E$24)*($C$5:$C$21=$D27)),$D$4:$R$4=$E$25)

 

[Shawn09]

Oh thank you so much! I knew it was something silly. And thanks for the alternative method... I've never used the filter function before so I'll have to play around with that. It looks pretty intuitive! Thanks again!

 

[Fluff]

Glad to help & thanks for the feedback.