Nested If/Or statement with a VLookup based off a list using first 2 characters

[Deelof]

I want to extend my current formula to add a condition using a list based off the first 2 characters of the variant SKU.
Any suggestions?

Status	Published	Variant SKU	RESULT	CURRENT FORMULA	ADDITION I WANT TO MAKE AS NESTED
Archived	FALSE​	BK00001-1	NOT TO GO ON	=IF(OR(F6="ACTIVE",G6="TRUE"),"ON","NOT ON")	LEFT 2 CHARACTERS AGAINST THE LOOKUP LIST MATCH AND RETURN "NOT TO GO ON"
Active	TRUE​	CH00001-1	ON
Draft	FALSE​	BT00001-1	NOT ON
Active	TRUE​	MW00002-1	NOT TO GO ON
Archived	FALSE​	AR00001-1	NOT ON

VLOOKUP LIST
BK	NOT TO GO ON
BU	NOT TO GO ON
CK	NOT TO GO ON
DW	NOT TO GO ON
FF	NOT TO GO ON
FP	NOT TO GO ON
GM	NOT TO GO ON
HD	NOT TO GO ON
IB	NOT TO GO ON
IR	NOT TO GO ON
KT	NOT TO GO ON
MW	NOT TO GO ON
SF	NOT TO GO ON
STA	NOT TO GO ON
TS	NOT TO GO ON
TV	NOT TO GO ON
VC	NOT TO GO ON
WM	NOT TO GO ON

 

[SanjayGMusafir]

a condition using a list based off the first 2 characters of the variant SKU

Test and Add -

--Lookup(Left(C2,2),Lookup Range)>1,"Not to Go On"

 

[Deelof]

Test and Add -

--Lookup(Left(C2,2),Lookup Range)>1,"Not to Go On"

Is that to go before of after my current formula? =IF(OR(F6="ACTIVE",G6="TRUE"),"ON","NOT ON")

=Lookup(Left(C2,2),Lookup Range)>1,"Not to Go On",IF(OR(F6="ACTIVE",G6="TRUE"),"ON","NOT ON")
=IF(OR(F6="ACTIVE",G6="TRUE"),"ON","NOT ON"),Lookup(Left(C2,2),Lookup Range)>1,"Not to Go On"

 

[SanjayGMusafir]

=IF(OR(F6="ACTIVE",G6="TRUE"),"ON",Lookup(Left(C2,2),Lookup Range)>1,"Not to Go On",0)

OR

=IF(Lookup(Left(C2,2),Lookup Range)>1,"Not to Go On",OR(F6="ACTIVE",G6="TRUE"),"ON",0)

Like above but Lookup range should be defined

Moreover the order has to be your order of preference

 

[Deelof]

=IF(OR(F6="ACTIVE",G6="TRUE"),"ON",Lookup(Left(C2,2),Lookup Range)>1,"Not to Go On",0)

OR

=IF(Lookup(Left(C2,2),Lookup Range)>1,"Not to Go On",OR(F6="ACTIVE",G6="TRUE"),"ON",0)

Like above but Lookup range should be defined

Moreover the order has to be your order of preference

So I have 3 results I want returning: ON / NOT ON / NOT TO GO ON.
Also if C is blank I want NOT ON to be returned.

 

[SanjayGMusafir]

So I have 3 results I want returning: ON / NOT ON / NOT TO GO ON.
Also if C is blank I want NOT ON to be returned.

Share the actual formula you are using...

I will help you improving it to your needs

 

[Deelof]

Share the actual formula you are using...

I will help you improving it to your needs

I am only currently using this: =IF(OR(F6="ACTIVE",G6="TRUE"),"ON","NOT ON") which only returns On or NOT ON based off columns A and B.
I want to extend this to also use the list and "NOT TO GO ON" based off the 1st 2 characters of column C's code.

 

[SanjayGMusafir]

How can I help you further unless you try and set up a new formula and come up with next challenge...

We are not working in an imaginary situation, so populate a new formula and then come up with the challenges remaining.

 

[Fluff]

unless you try and set up a new formula

That is what the OP is asking for help with.

 

[Fluff]

How about

Fluff.xlsm
	F	G	H	I	J	K	L	M	N	O	P
1											VLOOKUP LIST
2											BK
3											BU
4											CK
5	Status	Published	Variant SKU	RESULT							DW
6	Archived	FALSE	BK00001-1	NOT TO GO ON	Not to go on						FF
7	Active	TRUE	CH00001-1	ON	ON						FP
8	Draft	FALSE	BT00001-1	NOT ON	NOT ON						GM
9	Active	TRUE	MW00002-1	NOT TO GO ON	Not to go on						HD
10	Archived	FALSE	AR00001-1	NOT ON	NOT ON						IB
11											IR
12											KT
13											MW
14											SF
15											STA
16											TS
17											TV
18											VC
19											WM
Sheet7

Cell Formulas
Range	Formula
J6:J10	J6	=IF(ISNUMBER(MATCH(LEFT(H6,2),$P$2:$P$19,0)),"Not to go on",IF(OR(F6="ACTIVE",G6="TRUE"),"ON","NOT ON"))