Please help
I am trying to correct the below formula as I would like to use INDEX MODE MATCH with INDIRECT in a array.
The below works, but if I enter a new line at the top of my data set (EIRF Tracker Row 9-2150) the MODE and MATCH arguments change to read from row 10-2151 still.
{=INDEX(INDIRECT("'EIRF Tracker '!$N$9:$N$2153"),MODE(IF('EIRF Tracker '!$B$9:$B$2151="2018-19",IF('EIRF Tracker '!$C$9:$C$2151=C54,IF('EIRF Tracker '!$N$9:$N$2151<>"",MATCH('EIRF Tracker '!$N$9:$N$2151,'EIRF Tracker '!$N$9:$N$2151,0))))))}
So I attempted this but it is not a valid argument and I am unsure as to why.
{=INDEX(INDIRECT("'EIRF Tracker '!$N$9:$N$2153"),MODE(IF(indirect("'EIRF Tracker '!$B$9:$B$2151"),"2018-19",IF(Indirect("'EIRF Tracker '!$C$9:$C$2151"),C54,IF(indirect("'EIRF Tracker '!$N$9:$N$2151<>"",MATCH('EIRF Tracker '!$N$9:$N$2151),Indirect("'EIRF Tracker '!$N$9:$N$2151"),0))))))}
thank you!
I am trying to correct the below formula as I would like to use INDEX MODE MATCH with INDIRECT in a array.
The below works, but if I enter a new line at the top of my data set (EIRF Tracker Row 9-2150) the MODE and MATCH arguments change to read from row 10-2151 still.
{=INDEX(INDIRECT("'EIRF Tracker '!$N$9:$N$2153"),MODE(IF('EIRF Tracker '!$B$9:$B$2151="2018-19",IF('EIRF Tracker '!$C$9:$C$2151=C54,IF('EIRF Tracker '!$N$9:$N$2151<>"",MATCH('EIRF Tracker '!$N$9:$N$2151,'EIRF Tracker '!$N$9:$N$2151,0))))))}
So I attempted this but it is not a valid argument and I am unsure as to why.
{=INDEX(INDIRECT("'EIRF Tracker '!$N$9:$N$2153"),MODE(IF(indirect("'EIRF Tracker '!$B$9:$B$2151"),"2018-19",IF(Indirect("'EIRF Tracker '!$C$9:$C$2151"),C54,IF(indirect("'EIRF Tracker '!$N$9:$N$2151<>"",MATCH('EIRF Tracker '!$N$9:$N$2151),Indirect("'EIRF Tracker '!$N$9:$N$2151"),0))))))}
thank you!