Re: Error returned (but not 100% of the time) when using IF with MATCH and INDEX

Hi NBVC,

Many, many thanks for the reply. The first section is perfect (might I ask what the ROUND helps with?).

Now, with the buffer formula, I was trying to simplify it until I could work out why the formulas didn't work. I thought the ,"0" at the end was the "if it's not over then put 0". However, it appears I had missed the qualifier for the MATCH function out and, I think the ,"0" was being used for that. So, my new formula is

=IF(F6="Over",((E6-(D6-1/24))+(D6>E6))-INDEX($B$1:$B$24,MATCH(ROUND(((D6-1/24)+(D6>E6)),15),$A$1:$A$24,0))),IF(F6="Under",INDEX($B$1:$B$24,MATCH(ROUND(((D6-1/24)+(D6>E6)),15),$A$1:$A$24,0)),"0")

but it's returning #N/A

I'm sure the IF statement is right ie if "Over" perform this, if "Under" perform that, if anything else display 0. I know I've now incorporated D5>E5 to cover timings crossing midnight but that shouldn't be a problem, should it.

Thanks very much for what you've provided so far and, if my problem is syntax or not counting ( correctly just tell me to re-check my formula. If it's something more complicated, any help would be appreciated.

Tim