Mar 23 2020 03:33 AM
Mar 23 2020 04:08 AM
@Sergei BaklanPlease see attached file
Mar 23 2020 04:39 AM
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
Mar 23 2020 04:41 AM
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.
Mar 23 2020 05:02 AM
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"
)