Merging 2 Formulas

[AndyPorter]

Hi,

I use the following formula to look up whether or not the contents of specified cells fall between the parameters in a look up table.

Code: Select all

=IF(ISERROR(IF(OR(T769<VLOOKUP(V769;$AB$5:$AD$26;2;0);U769>VLOOKUP(V769;$AB$5:$AD$26;3;0));1;0));"";IF(OR(T769<VLOOKUP(V769;$AB$5:$AD$26;2;0);U769>VLOOKUP(V769;$AB$5:$AD$26;3;0));1;0))

Columns T and U contain a time and column V contains a name. Likewise columns AB and AD contain reference times and AD contains the reference name.

This formula works perfectly however I now need to amend it so that if the name in column V equals 'Floor Sweep', 'Floor Wash', 'Toilets', 'Restaurant' or 'Rubbish / Spillages' it returns 0. If the name equals something else the formula would then continue as per the original formula outlined above.

Your help will be greatly appreciated!

[FJCC]

Try this formula. I changed the error check to use the MATCH function since I think you just want to confirm that V769 appears in the range AB5:AB26.

Code: Select all

IF(ISNUMBER(MATCH(V769;{"Floor Sweep";"Floor Wash";"Toilets";"Restaurant";"Rubbish / Spillages"};0));0; IF(ISERROR(MATCH(V769;$AB$5:$AB$26;0));"";IF(OR(T769<VLOOKUP(V769;$AB$5:$AD$26;2;0);U769>VLOOKUP(V769;$AB$5:$AD$26;3;0));1;0))

[FJCC]

By the way, since OR() returns TRUE or FALSE and TRUE = 1 and FALSE = 0, you can get rid of that last IF().

Code: Select all

IF(ISNUMBER(MATCH(V769;{"Floor Sweep";"Floor Wash";"Toilets";"Restaurant";"Rubbish / Spillages"};0));0; IF(ISERROR(MATCH(V769;$AB$5:$AB$26;0));"";OR(T769<VLOOKUP(V769;$AB$5:$AD$26;2;0);U769>VLOOKUP(V769;$AB$5:$AD$26;3;0)))