Help with 'If' function

[TBA]

Hi there all, sorry I just can’t get my head around nested ifs??
I have two columns I and K and the following criteria with desired return
for the formula:
I and K can be one of these values “N/Câ€, “Startâ€, “Stopâ€, “Incâ€, “Decâ€,
“BLANKâ€, “C/Râ€
Depending on the combination of I and K, I want to return either “Yes†or
‘Noâ€.
Here are the I and K = outcome options:
N/C and N/C = Yes
N/C and Start= Yes
N/C and Stop = No
N/C and Inc = Yes
N/C and Dec = Yes
N/C and BLANK= Yes
N/C and C/R = Yes
BLANK and BLANK = No
Start and N/C= No
Start and Stop = No
Stop and N/C= Yes
Inc and N/C=Yes
Inc and Dec= Yes

There are more!

Can i combine all of the ‘Yes’ options? What’s the best or easiest way to
deal with this? Many thanks Theo.

 

[Pete_UK]

I would suggest that you build up a 2-column table somewhere showing
all those combinations in one column and the desired outcome in the
other column like this:

N/CN/C Yes
N/CStart Yes
N/CStop No
N/CInc Yes
N/CDec Yes
N/CBLANK Yes
N/CC/R Yes
BLANKBLANK No
StartN/C No
StartStop No
StopN/C Yes
IncN/C Yes
IncDec Yes

Suppose this is in X1:Y13.

Then you can just use this formula on row 2:

=VLOOKUP(I2&K2,X$1:Y$13,2,0)

and copy down as required.

Hope this helps.

Pete

 

[TBA]

thanks Pete-0 alot more straight forward than I feared! works a treat, here's
to GMT- saves me every time...

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

thanks Pete-0 alot more straight forward than I feared! works a treat, here's
to GMT- saves me every time...









- Show quoted text -