Multiple If statements too many arguments

[ItalianPlatinum]

Hello - I am at capacity on using if statements and not sure how to change my formula to include another one.

On the below really looking to just add one more criteria that if row D is 98 it needs to use CCC or CCC-. instead of DDD or DDD-. Right now i manually adjust it to correct. but want to find a way it auto does it. can someone help? i am fine using VBA too if easier

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1										F	Amount	Type	Amount	Type
2										5000	$ 10,000.00	Y	$ 5,000.00	N
3										6000	$ 9,000.00	Y	$ 5,000.00	N
4										7000	$ 50,000.00	Y	$ 5,000.00	N
5
6	Date	F	N	C	R	F	C	T	Amount	Type	Comment	F	C	T	Amount	Type	Comment
7	3-Nov	5000	Intl	27	25%	5000	27	DDD	2,500.00	Y		5000	27	DDD	1,250.00	N
8	3-Nov	5000	Intl	31	50%	5000	31	DDD	5,000.00	Y		5000	31	DDD	2,500.00	N
9	3-Nov	5000	Intl	98	25%	5000	98	DDD	2,500.00	Y		5000	98	DDD	1,250.00	N
10	3-Nov	6000	Dom	27	20%	6000	27	DDD	1,800.00	Y		6000	27	DDD	1,000.00	N
11	3-Nov	6000	Dom	31	20%	6000	31	DDD	1,800.00	Y		6000	31	DDD	1,000.00	N
12	3-Nov	6000	Dom	98	40%	6000	98	DDD	3,600.00	Y		6000	98	DDD	2,000.00	N
13	3-Nov	7000	ID	27	10%	7000	27	DDD	5,000.00	Y		7000	27	DDD	500.00	N
14	3-Nov	7000	ID	31	80%	7000	31	DDD	40,000.00	Y		7000	31	DDD	4,000.00	N
15	3-Nov	7000	ID	98	10%	7000	98	DDD	5,000.00	Y		7000	98	DDD	500.00	N
Sheet3

Cell Formulas
Range	Formula
F7:F15	F7	=IF(VLOOKUP(B7,$J$1:$L$4,2,FALSE)="","",VLOOKUP(B7,$J$1:$L$4,1,FALSE))
G7:G15	G7	=IF(VLOOKUP(B7,$J$1:$L$4,2,FALSE)="","",D7)
H7:H15	H7	=IF(VLOOKUP(B7,$J$1:$L$4,2,FALSE)="","",IF(VLOOKUP(B7,$J$1:$L$4,2,FALSE)>0,"DDD","DDD-"))
I7:I15	I7	=IF((VLOOKUP(B7,$J$1:$L$4,2,FALSE))="","",ROUND(ABS((VLOOKUP(B7,$J$1:$L$4,2,FALSE)))*E7,2))
J7:J15	J7	=IF(VLOOKUP(B7,$J$1:$L$4,2,FALSE)="","",VLOOKUP(B7,$J$1:$L$4,3,FALSE))
L7:L15	L7	=IF(VLOOKUP(B7,$J$1:$N$4,2,FALSE)="","",VLOOKUP(B7,$J$1:$N$4,1,FALSE))
M7:M15	M7	=IF(VLOOKUP(B7,$J$1:$N$4,4,FALSE)="","",D7)
N7:N15	N7	=IF(VLOOKUP(B7,$J$1:$N$4,4,FALSE)="","",IF(VLOOKUP(B7,$J$1:$N$4,4,FALSE)>0,"DDD","DDD-"))
O7:O15	O7	=IF((VLOOKUP(B7,$J$1:$N$4,4,FALSE))="","",ROUND(ABS((VLOOKUP(B7,$J$1:$N$4,4,FALSE)))*E7,2))
P7:P15	P7	=IF(VLOOKUP(B7,$J$1:$N$4,2,FALSE)="","",VLOOKUP(B7,$J$1:$N$4,5,FALSE))

 

[StephenCrump]

A bit of a guess, because the question isn't clear ...

Does this do what you want:

H7: =IFERROR(IF(D7=98,"CCC","DDD")&IF(VLOOKUP(B7,$J$2:$L$4,2,)>0,"","-"),"")

 

[ItalianPlatinum]

A bit of a guess, because the question isn't clear ...

Does this do what you want:

H7: =IFERROR(IF(D7=98,"CCC","DDD")&IF(VLOOKUP(B7,$J$2:$L$4,2,)>0,"","-"),"")

Basically looking for

1. If no amount of the respective "F" in K2:K4 then D will be blank
   1. If not blank and column "C" = 98 then if amount >0 do CCC, if not CCC-
   2. If not 98 then if amount > 0 do DDD, if not DDD-

 

[StephenCrump]

Basically looking for

1. If no amount of the respective "F" in K2:K4 then D will be blank
   1. If not blank and column "C" = 98 then if amount >0 do CCC, if not CCC-
   2. If not 98 then if amount > 0 do DDD, if not DDD-

OK. Did you try the formula I gave for cell H7?

 

[ItalianPlatinum]

OK. Did you try the formula I gave for cell H7?

Hi Stephen works except for If no amount of the respective "F" in K2:K4 then D will be blank

which i think is my fault i didn't have that in my mockup. so sorry

 

[StephenCrump]

Hi Stephen works except for If no amount of the respective "F" in K2:K4 then D will be blank

Sorry, I am not clear what you mean. My formula for H7 will return blank if there is no match for B7 in J$2:J$4, or if B7 is blank.

If you want something different, can you please show an example?

 

[ItalianPlatinum]

Sorry, I am not clear what you mean. My formula for H7 will return blank if there is no match for B7 in J$2:J$4, or if B7 is blank.

If you want something different, can you please show an example?

apologies see below

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1										F	Amount	Type	Amount	Type
2										5000		Y	$ 5,000.00	N
3										6000	$(10,000.00)	Y	$ 5,000.00	N
4										7000	$(10,000.00)	Y	$ 5,000.00	N
5
6	Date	F	N	C	R	F	C	T	Amount	Type	Comment	F	C	T	Amount	Type	Comment
7	3-Nov	5000	Intl	27	25%			DDD-					27	DDD	1,250.00
8	3-Nov	5000	Intl	31	50%			DDD-					31	DDD	2,500.00
9	3-Nov	5000	Intl	98	25%			CCC-					98	DDD	1,250.00
10	3-Nov	6000	Dom	27	20%	6000	27	DDD-	2,000.00	Y		6000	27	DDD	1,000.00	N
11	3-Nov	6000	Dom	31	20%	6000	31	DDD-	2,000.00	Y		6000	31	DDD	1,000.00	N
12	3-Nov	6000	Dom	98	40%	6000	98	CCC-	4,000.00	Y		6000	98	DDD	2,000.00	N
13	3-Nov	7000	ID	27	10%	7000	27	DDD-	1,000.00	Y		7000	27	DDD	500.00	N
14	3-Nov	7000	ID	31	80%	7000	31	DDD-	8,000.00	Y		7000	31	DDD	4,000.00	N
15	3-Nov	7000	ID	98	10%	7000	98	CCC-	1,000.00	Y		7000	98	DDD	500.00	N
Sheet3

Cell Formulas
Range	Formula
F7:F15	F7	=IF(VLOOKUP(B7,$J$1:$L$4,2,FALSE)="","",VLOOKUP(B7,$J$1:$L$4,1,FALSE))
G7:G15	G7	=IF(VLOOKUP(B7,$J$1:$L$4,2,FALSE)="","",D7)
H7:H15	H7	=IFERROR(IF(D7=98,"CCC","DDD")&IF(VLOOKUP(B7,$J$2:$L$4,2,)>0,"","-"),"")
I7:I15	I7	=IF((VLOOKUP(B7,$J$1:$L$4,2,FALSE))="","",ROUND(ABS((VLOOKUP(B7,$J$1:$L$4,2,FALSE)))*E7,2))
J7:J15	J7	=IF(VLOOKUP(B7,$J$1:$L$4,2,FALSE)="","",VLOOKUP(B7,$J$1:$L$4,3,FALSE))
L7:L15	L7	=IF(VLOOKUP(B7,$J$1:$N$4,2,FALSE)="","",VLOOKUP(B7,$J$1:$N$4,1,FALSE))
M7:M15	M7	=IF(VLOOKUP(B7,$J$1:$N$4,4,FALSE)="","",D7)
N7:N15	N7	=IF(VLOOKUP(B7,$J$1:$N$4,4,FALSE)="","",IF(VLOOKUP(B7,$J$1:$N$4,4,FALSE)>0,"DDD","DDD-"))
O7:O15	O7	=IF((VLOOKUP(B7,$J$1:$N$4,4,FALSE))="","",ROUND(ABS((VLOOKUP(B7,$J$1:$N$4,4,FALSE)))*E7,2))
P7:P15	P7	=IF(VLOOKUP(B7,$J$1:$N$4,2,FALSE)="","",VLOOKUP(B7,$J$1:$N$4,5,FALSE))

 

[StephenCrump]

So because K2 is blank, you want blank in H7:H9?

Try H7: =IFERROR(IF(D7=98,"CCC","DDD")&IF(1/VLOOKUP(B7,$J$2:$L$4,2,)>0,"","-"),"")

I haven't looked at your formula in N7 yet. Is it doing what you want?

 

[ItalianPlatinum]

apologies see below

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1										F	Amount	Type	Amount	Type
2										5000		Y	$ 5,000.00	N
3										6000	$(10,000.00)	Y	$ 5,000.00	N
4										7000	$(10,000.00)	Y	$ 5,000.00	N
5
6	Date	F	N	C	R	F	C	T	Amount	Type	Comment	F	C	T	Amount	Type	Comment
7	3-Nov	5000	Intl	27	25%			DDD-					27	DDD	1,250.00
8	3-Nov	5000	Intl	31	50%			DDD-					31	DDD	2,500.00
9	3-Nov	5000	Intl	98	25%			CCC-					98	DDD	1,250.00
10	3-Nov	6000	Dom	27	20%	6000	27	DDD-	2,000.00	Y		6000	27	DDD	1,000.00	N
11	3-Nov	6000	Dom	31	20%	6000	31	DDD-	2,000.00	Y		6000	31	DDD	1,000.00	N
12	3-Nov	6000	Dom	98	40%	6000	98	CCC-	4,000.00	Y		6000	98	DDD	2,000.00	N
13	3-Nov	7000	ID	27	10%	7000	27	DDD-	1,000.00	Y		7000	27	DDD	500.00	N
14	3-Nov	7000	ID	31	80%	7000	31	DDD-	8,000.00	Y		7000	31	DDD	4,000.00	N
15	3-Nov	7000	ID	98	10%	7000	98	CCC-	1,000.00	Y		7000	98	DDD	500.00	N
Sheet3

Cell Formulas
Range	Formula
F7:F15	F7	=IF(VLOOKUP(B7,$J$1:$L$4,2,FALSE)="","",VLOOKUP(B7,$J$1:$L$4,1,FALSE))
G7:G15	G7	=IF(VLOOKUP(B7,$J$1:$L$4,2,FALSE)="","",D7)
H7:H15	H7	=IFERROR(IF(D7=98,"CCC","DDD")&IF(VLOOKUP(B7,$J$2:$L$4,2,)>0,"","-"),"")
I7:I15	I7	=IF((VLOOKUP(B7,$J$1:$L$4,2,FALSE))="","",ROUND(ABS((VLOOKUP(B7,$J$1:$L$4,2,FALSE)))*E7,2))
J7:J15	J7	=IF(VLOOKUP(B7,$J$1:$L$4,2,FALSE)="","",VLOOKUP(B7,$J$1:$L$4,3,FALSE))
L7:L15	L7	=IF(VLOOKUP(B7,$J$1:$N$4,2,FALSE)="","",VLOOKUP(B7,$J$1:$N$4,1,FALSE))
M7:M15	M7	=IF(VLOOKUP(B7,$J$1:$N$4,4,FALSE)="","",D7)
N7:N15	N7	=IF(VLOOKUP(B7,$J$1:$N$4,4,FALSE)="","",IF(VLOOKUP(B7,$J$1:$N$4,4,FALSE)>0,"DDD","DDD-"))
O7:O15	O7	=IF((VLOOKUP(B7,$J$1:$N$4,4,FALSE))="","",ROUND(ABS((VLOOKUP(B7,$J$1:$N$4,4,FALSE)))*E7,2))
P7:P15	P7	=IF(VLOOKUP(B7,$J$1:$N$4,2,FALSE)="","",VLOOKUP(B7,$J$1:$N$4,5,FALSE))

works great. thank you