Preacherman771
New Member
 Joined
 Jun 15, 2021
 Messages
 36
 Office Version

 365
 Platform

 Windows
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 20212022 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. 