Forum Discussion
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
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
Could you provide sample file?
SergeiBaklanPlease see attached file
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" )
