Error when using Index Match formula

[Preacherman771]

I am stumped when using an INDEX MATCH formula which I have used many times. I am trying to find the value of two given items, "2" & "Bal". When using =INDEX($AA$8:$AP$8,MATCH(1,(AD$15=$AA$7:$AP$7)*($Z8=$Z$7:$Z$14),0)), as I have always, I receive "N/A" as the result. I would appreciate a fresh set of eyes to find what I am missing and/or writing wrong. I intend to once corrected, to include IFERROR in order to catch the blank cells.

NFL 2021-2022 Standings (Template).xlsx
	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP
7	1	KC	BUF	PIT	BAL				MIA	LV	NE		DEN	CIN	HOU	NYJ	JAX
8	2				CLE						LAC
9	3				IND
10	4				TEN
11	5
12	6
13	7
14	8
15		KC	BUF	PIT	BAL	MIA	LV	NE	DEN	CIN	HOU	NYJ	JAX
16		#N/A	##	#N/A	##	#N/A	##	#N/A	##	#N/A	##	#N/A	##
17		#N/A	##	#N/A	##	#N/A	##	#N/A	##	#N/A	##	#N/A	##
18		#N/A	##	#N/A	##	#N/A	##	#N/A	##	#N/A	##	#N/A	##
19		#N/A	##	#N/A	##	#N/A	##	#N/A	##	#N/A	##	#N/A	##
20		#N/A	##	#N/A	##	#N/A	##	#N/A	##	#N/A	##	#N/A	##
21		#N/A	##	#N/A	##	#N/A	##	#N/A	##	#N/A	##	#N/A	##
22		#N/A	##	#N/A	##	#N/A	##	#N/A	##	#N/A	##	#N/A	##
Calc_Ties (Conf)

Cell Formulas
Range	Formula
AA7	AA7	=IF(T7<>T6,FILTER($R$7:$R$22,$T$7:$T$22=$T7),"")
AB7	AB7	=IF(T8<>T7,FILTER($R$7:$R$22,$T$7:$T$22=$T8),"")
AC7	AC7	=IF(T9<>T8,FILTER($R$7:$R$22,$T$7:$T$22=$T9),"")
AD7:AD10	AD7	=IF(T10<>T9,FILTER($R$7:$R$22,$T$7:$T$22=$T10),"")
AE7	AE7	=IF(T11<>T10,FILTER($R$7:$R$22,$T$7:$T$22=$T11),"")
AF7	AF7	=IF(T12<>T11,FILTER($R$7:$R$22,$T$7:$T$22=$T12),"")
AG7	AG7	=IF(T13<>T12,FILTER($R$7:$R$22,$T$7:$T$22=$T13),"")
AH7	AH7	=IF(T14<>T13,FILTER($R$7:$R$22,$T$7:$T$22=$T14),"")
AI7	AI7	=IF(T15<>T16,FILTER($R$7:$R$22,$T$7:$T$22=$T15),"")
AJ7:AJ8	AJ7	=IF(T16<>T15,FILTER($R$7:$R$22,$T$7:$T$22=$T16),"")
AK7	AK7	=IF(T17<>T16,FILTER($R$7:$R$22,$T$7:$T$22=$T17),"")
AL7	AL7	=IF(T18<>T17,FILTER($R$7:$R$22,$T$7:$T$22=$T18),"")
AM7	AM7	=IF(T19<>T18,FILTER($R$7:$R$22,$T$7:$T$22=$T19),"")
AN7	AN7	=IF(T20<>T19,FILTER($R$7:$R$22,$T$7:$T$22=$T20),"")
AO7	AO7	=IF(T21<>T20,FILTER($R$7:$R$22,$T$7:$T$22=$T21),"")
AP7	AP7	=IF(T22<>T21,FILTER($R$7:$R$22,$T$7:$T$22=$T22),"")
AA15:AP15	AA15	=IF(COLUMNS($AA$15:AA15)+COUNTIF($AA$7:AA7,"")>=17,"", IF(AA$7<>"",(IF(COUNTIF($Z$15:Z15,AA$7)<>1,AA$7,INDEX($AA$7:$AP$7,SMALL(IF($AA$7:$AP$7<>"",COLUMN($AA$7:$AP$7)-COLUMN($AA$7)+1),AA6)))), INDEX($AA$7:$AP$7,SMALL(IF($AA$7:$AP$7<>"",COLUMN($AA$7:$AP$7)-COLUMN($AA$7)+1),AA6))))
AA16:AL22	AA16	=INDEX($AA$8:$AP$8,MATCH(1,(AA$15=$AA$7:$AP$7)*($Z8=$Z$7:$Z$14),0))
Dynamic array formulas.

 

[Fluff]

Not entirely sure, what you are tring to do with that formula, but does this do what you want.

=INDEX($AA$8:$AP$14,MATCH($Z8,$Z$8:$Z$14,0),MATCH(AA$15,$AA$7:$AP$7,0))

 

[RoryA]

Match only works with a 1 column/row range (or array). Your criteria is multiplying a vertical array by a horizontal one, which creates a 2 d array. It looks to me like you want a regular INDEX(..,MATCH(), MATCH) formula?

 

[Preacherman771]

Walked away and relooked at my situation and discovered the error. Since I'm indexing the row which is the value "2", I don't need to MATCH it. INDEX($AA$8:$AP$8,MATCH(AA$15,$AA$7:$AP$7,0)). Duh!!!

 

[Preacherman771]

Thanks for all of the replies.

 

[Fluff]

Glad you sorted it & thanks for the feedback.