Excel Formula with INDEX returning #REF! error but cannot see why, checked everything

Copper Contributor

Excel Formula with INDEX returning #REF! error but cannot see why, checked everything

1 Reply

@Michael2024 

 

I solved the problem...the range I used was incorrect, probably caused by a copy and paste or by deleting columns. 

 

Refer to bolded & underlined items below. I should have used AP instead of AG. 

 

Gives #REF! error :

 

=INDEX('Exec Data'!$E$4:$AG$99926, MATCH($A69,'Exec Data'!$A$4:$A$99926,0),MATCH(AN$68,'Exec Data'!$E$3:$AP$3,0))

 

Resolved the #REF! error :

 

=INDEX('Exec Data'!$E$4:$AP$99926, MATCH($A69,'Exec Data'!$A$4:$A$99926,0),MATCH(AN$68,'Exec Data'!$E$3:$AP$3,0))

 

Excel should have been smart enough to work this out !!!