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)

 

[Wishmaster89]

It works, thanks alot @Fluff

 

[Fluff]

You're welcome & thanks for the feedback.