Nested if with or & and

[Wishmaster89]

Hi Everyone

I have attempted to write a formula that will check if column E and H OR E and J are not blank and then do a vlookup to get a price. I am having trouble with the first part.
Here is my attempted formula so far but it returns False at the moment(Cell L4)

=IF(OR(AND(E4="Member",H4="<>")),VLOOKUP(H4,'CII Costs'!B:C,2,0),IF(AND(E4="Non - Member",H4="<>"),VLOOKUP(H4,'CII Costs'!B:D,3,0)))

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	NEW CII WORKBOOK ORDERING FORM
2	To be completed by coach											For Ops use
3	Date of order	Learner first name	Learner surname	Full address for delivery	CII member/Non-member	CII PIN	Qualification	Workbook request	IP L3 - route/	IP L3 option	Which optional unit?	Cost £	Postage cost £	Total cost £	Qualification	IP L3 - route/option	Aon Y/N	Brewin Dolphin Y/N	RSA Y/N	Ops Comments
4					Member			IF2	Broker - Option 2 - Cert C11 with LM1	EP2	IF5 enrolment plus	FALSE		0
5					Non - Member				Broker - Option 1 - Cert CII with IF1	Optional unit	IF6 enrolment plus	#N/A		#N/A
6					Member				Underwriting - Option 2 - Cert CII		IF1 enrolment plus	#N/A		#N/A
7					Non - Member				Broker - Option 2 - Cert C11 with LM1		IF6 enrolment plus	#N/A		#N/A
8					Member				Senior Financial Services Customer L3 - Opton 1 - Cert II			#N/A		#N/A
9					Non - Member							#N/A		#N/A
10					Non - Member				Broker - Option 1 - Cert CII with IF1	Optional unit		#N/A		#N/A
11					Member							#N/A		#N/A
12					Non - Member				Broker - Option 1 - Cert CII with IF1	Optional unit		#N/A		#N/A
13					Member				Underwriting - Option 1 - Cert CII			#N/A		#N/A
14					Non - Member				Underwriting - Option 2 - Cert CII			#N/A		#N/A
15					Member				Underwriting - Option 1 - Cert CII			#N/A		#N/A
16					Non - Member				Underwriting - Option 1 - Cert CII	IF3		#N/A		#N/A
17					Non - Member				Broker - Option 2 - Cert C11 with LM1			#N/A		#N/A
18					Member				Broker - Option 1 - Cert CII with IF1			#N/A		#N/A
19					Non - Member				Claims / Loss adjuster Option 3 -CILA	CH2		#N/A		#N/A
20					Member				Broker - Option 1 - Cert CII with IF1	Optional unit		#N/A		#N/A
21					Non - Member							#N/A		#N/A
22
Sheet1

Cell Formulas
Range	Formula
L4	L4	=IF(OR(AND(E4="Member",H4="<>")),VLOOKUP(H4,'[Copy of Copy of Copy of NEW CII ORDER SPREADSHEET 2022.xlsx]CII Costs'!B:C,2,0),IF(AND(E4="Non - Member",H4="<>"),VLOOKUP(H4,'[Copy of Copy of Copy of NEW CII ORDER SPREADSHEET 2022.xlsx]CII Costs'!B:D,3,0)))
L5	L5	=IFS(E5="Member",VLOOKUP(H5,'[Copy of Copy of Copy of NEW CII ORDER SPREADSHEET 2022.xlsx]CII Costs'!B:C,2,FALSE),E5="Non - Member",VLOOKUP(J5,'[Copy of Copy of Copy of NEW CII ORDER SPREADSHEET 2022.xlsx]CII Costs'!B:D,3,FALSE))
L6:L21	L6	=IFS(E6="Member",VLOOKUP(H6,'[Copy of Copy of Copy of NEW CII ORDER SPREADSHEET 2022.xlsx]CII Costs'!B:C,2,FALSE),E6="Non - Member",VLOOKUP(H6,'[Copy of Copy of Copy of NEW CII ORDER SPREADSHEET 2022.xlsx]CII Costs'!B:D,3,FALSE))
N4:N21	N4	=SUM(L4:M4)

 

[Fluff]

How about

=IF(AND(E4="Member",H4="<>"),VLOOKUP(H4,'CII Costs'!B:C,2,0),IF(AND(E4="Non - Member",H4="<>"),VLOOKUP(H4,'CII Costs'!B:D,3,0),""))

 

[Wishmaster89]

Thanks but i also need to check if a and j are not blank as well.
H and J can’t both have values, i’ve got some vba code that will sort that out though.

 

[Fluff]

In that case what are all the eventualities & what should happen for each.

 

[Wishmaster89]

So if h and j have values i’ll do a vlookup based on the those values to another sheet. The vlookup part is easy. I just need to write the first part that checks if there is a value in e and h or e and j - hope that makes sense

 

[Fluff]

hope that makes sense

Not realy, what is the order to check & what should happen if something is true?

 

[Wishmaster89]

If e4=member and h4 has a value then do a vlookup

If e4=non member and h4 has a value then do a vlookup

If e4=member and j4 has a value then do a vlookup

If e4= non member and j4 has a value then do a vlook up

Hope this helps

 

[Fluff]

Ok, how about

=IF(AND(E4="Member",H4="<>"),VLOOKUP(H4,'CII Costs'!B:C,2,0),IF(AND(E4="Non - Member",H4="<>"),VLOOKUP(H4,'CII Costs'!B:D,3,0),IF(AND(E4="Member",J4="<>"),VLOOKUP(H4,'CII Costs'!B:C,2,0),IF(AND(E4="Non - Member",J4="<>"),VLOOKUP(H4,'CII Costs'!B:D,3,0),""))))

 

[Wishmaster89]

Thanks, will try in the morning. I had similar logic but i got errors, maybe my syntax was wrong.

 

[Fluff]

Ok