Index function not working

Copper Contributor
Hi,

I have a table of postcodes in the UK and times that a vehicle can first reach in the top row as my header.

I have used a left function to break down the 4 possibilities from the postcode (ph42 becomes p, pH, ph4 and ph42 which I can use an if function on later) but my array function is not returning the correct headers and returning ones which are not related at all. The formula is below

=INDEX($A$1:$J$1,,MIN(IF($A$2:$J$1048576=M6,COLUMN(A:J))))

It has worked on a similar spreadsheet but not on this one and I cannot work out why.

Thanks
5 Replies

@Ashleighb2437 

Could you provide sample file?

@Sergei BaklanPlease see attached file

@Ashleighb2437 

If no matches IF() returns FALSE, taking MIN() we receive zero, INDEX returns entire row A1:J1, with non-dynamic array Excel it returns first element of it, i.e. 09:00

 

You may modify as

=IFERROR(INDEX($A$1:$J$1,,MIN(IF($A$2:$J$1048576=M6,COLUMN(A:J),1E+32))),"no such")

result is

image.png 

HI @Ashleighb2437 

 

May you can try writing the formula as below. When it don't find the value it will have #value error 

with earlier formula when add the min if it don't found the value it return as 0 which returns the entire row. 

 

=INDEX($A$1:$J$1,IF(MIN(IF(M12=$A$2:$J$400,COLUMN($A$1:$J$1)))=0,"",MIN(IF(M12=$A$2:$J$400,COLUMN($A$1:$J$1)))))

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official Answer.

@Ashleighb2437 

Another, and more safe variant, could be

=IF(
   SUMPRODUCT(($A$1:$J$10000=M6)*COLUMN($A$1:$J$10000)),
   INDEX($A$1:$J$1,1,SUMPRODUCT(($A$1:$J$10000=M6)*COLUMN($A$1:$J$10000))),
   "no such"
)