IF Does Not Seem to Understand

Hi Everyone,

I have a formula (or at least I'm trying to create one) which should compare a Cell value of Time (H:mm) (Cell C3) with that of other Cells of the same format.

The attached example will show that both formulas are returning a value, but if the time in the Source Cell is changed to a higher or lower time, this is not being reflected in the output cell. i.e. The value does not change.

My intention is to have only one formula which checks against all time values in the "Begin - End - Class" array for each and every entry in rows 3-16.

The values currently showing in Column E (Time-zone) have been entered manually as a check against that which the formula should return.
Column G is registering a value of 1 (but should be 2).

All/any help would be gratefully received as to why this formula is not working as required.

Thank you,

Ian

This formula will give you the appropriate Class codes:

=IFERROR(MATCH(C3,$A$20:$A$25),"")

Put it on row 3, then copy down as required.

Hope this helps.

Pete

Hi sweep,

What can I say -- but Thank You.
It not only works but, despite never having previously used "Index or Match" -- I can almost understand it !!!

Kind regards,

Ian

Hi Pete UK,

As with the reply from "sweep" --- Brilliant!!

I'm not only surrounded by geniuses, I am now spoilt for choice on formulas.

Many thanks,

Ian

Hi Norie,

Column E, where it shows 3 and not 4 as the TimeZone, is a clear illustration of my need for a formula as a formula is considerably more accurate than I. (In other words -- it was a mistake on my part -- well spotted.)

Thanks too for your formula response.

It certainly works but, I have to admit, I don't understand it -- but I shall investigate the mysteries of "LOOKUP" and learn.

Many thanks for your help.

Best regards,

Ian

Glad to help, Ian - thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete

Hi Pete_UK,

Sorry for the omission but yes, my question is well and truly solved.

Ian