Match with #value error

[longstick]

Thank you in advance for any help you can provide.

{=IF(B2="OK","",MIN(ABS(B3-INDEX($B$1:$K$1,0,MATCH(MIN(ABS(B3-$B$1:$K$1)),ABS(B3-$B$1:$K$1),0))),ABS(INDEX($B$1:$K$1,0,MATCH(MIN(ABS(B4-$B$1:$K$1)),ABS(B4-$B$1:$K$1),0))-B4)))}

Above is the code I am using to find the closest time checked to the check windows listed below that are being audited.
Once the closest time is found then I check the difference between the two.

All of the "Time When Checked" cells will contain a formula. It will either show a time or be blank but not empty. The cell will always have a formula in it.

I am receiving a #value error when the formula encounters the cells that have formulas in them but a value of "".
The data will always be listed left to right. The check times might not be in order of earliest to latest. And, any cells with no values will always be on the right side.

Time When Checked 6:42 7:20 8:00 10:15 10:18 11:44 12:21 13:14 14:29 15:07 NG OK OK NG OK OK OK OK OK OK Start Window 6:20 7:10 8:00 9:10 10:00 11:30 12:20 13:10 14:15 15:05 End Window 6:40 7:30 8:20 9:30 10:20 11:50 12:40 13:30 14:35 15:25 Check Formula 0:02 0:45 <tbody> </tbody>

<tbody>
</tbody>

 

[barry houdini]

Doesn't look to me like you need the complexity of INDEX/MATCH, try this formula in B5 which should give the same results but will also ignore blank cells in B1:K1 =IF(B2="OK","",MIN(IF($B$1:$K$1<>"",ABS(B3-$B$1:$K$1)),IF($B$1:$K$1<>"",ABS(B4-$B$1:$K$1)))) confirm with CTRL+SHIFT+ENTER and copy across

 

[longstick]

Doesn't look to me like you need the complexity of INDEX/MATCH, try this formula in B5 which should give the same results but will also ignore blank cells in B1:K1 =IF(B2="OK","",MIN(IF($B$1:$K$1<>"",ABS(B3-$B$1:$K$1)),IF($B$1:$K$1<>"",ABS(B4-$B$1:$K$1)))) confirm with CTRL+SHIFT+ENTER and copy across[/QUOTE] Works great! thank you very much.