 Highlighted

# Index function not working

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
Highlighted

# Re: Index function not working

Could you provide sample file?

Highlighted

Highlighted

# Re: Index function not working

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 Highlighted

# Re: Index function not working

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.

Highlighted

# Re: Index function not working

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"
)``````