Forum Discussion

Ashleighb2437's avatar
Ashleighb2437
Copper Contributor
Mar 23, 2020

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 (p...
excel
Formulas and Functions
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Mar 23, 2020

      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"
)
    • ExcelExciting's avatar
      ExcelExciting
      MVP
      Mar 23, 2020

      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.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Mar 23, 2020

      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

       

Resources