## Merging 2 Formulas

### Merging 2 Formulas

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   Expand viewCollapse view
`=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!
Last edited by robleyd on Thu Oct 10, 2019 12:05 pm, edited 1 time in total.
OpenOffice 2.4.2
Windows 7
AndyPorter

Posts: 4
Joined: Mon Dec 15, 2014 12:46 am

### Re: Merging 2 Formulas

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   Expand viewCollapse view
`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))`
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7607
Joined: Sat Nov 08, 2008 8:08 pm

### Re: Merging 2 Formulas

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   Expand viewCollapse view
`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)))`
Windows 10 and Linux Mint, since 2017