Index Match Cannot Find Data If Cell In Formula Format

[FauzanOmar]

Hi guys, i have problem to solve this index match formula.

Basically, i want to calculate how many hours can the flight crew will end their duty based on timing calendar.
The problem appear when i put the formula,

• 1st sector, the crew start duty at 7:00am, however the formula didn't catch the duty hours #N/A which fall in 13:00hrs from the timing table.
• 2nd sector, the crew start duty at 9:00am, and the formula catch the duty hours of 12:15hrs from the timing table.

How do i solve this issue? your kind help is appreciated! I attached together the excel if you want to see the formula use. Thanks

 

[vletm]

FauzanOmar
One short way is to modify Your row formula in cell T4 to MATCH(S4,B4:B9,1) to match less than instead of exact.
You could search the reason based below hint.

 

[bosco_yip]

Floating point problem in the time Column, the workaround is to use ROUND() in fixing the decimal numbers.

So,

In T4, array formula copied down :

=INDEX($C$4:$J$9,MATCH(ROUND(S4,4),ROUND($B$4:$B$9,4),0),MATCH(L4,$C$3:$J$3,0))

Array formula to be confirmed by pressing "Ctrl"+"Shift"+"Enter" 3 keystrokes altogether instead of just "Enter"

 

[FauzanOmar]

FauzanOmar
One short way is to modify Your row formula in cell T4 to MATCH(S4,B4:B9,1) to match less than instead of exact.
You could search the reason based below hint.
View attachment 78400

Thanks, it solve my problem

 

[FauzanOmar]

Floating point problem in the time Column, the workaround is to use ROUND() in fixing the decimal numbers.

So,

In T4, array formula copied down :

=INDEX($C$4:$J$9,MATCH(ROUND(S4,4),ROUND($B$4:$B$9,4),0),MATCH(L4,$C$3:$J$3,0))

Array formula to be confirmed by pressing "Ctrl"+"Shift"+"Enter" 3 keystrokes altogether instead of just "Enter"

View attachment 78401

thanks for your help! problem solved