IF Statement with multiple conditions - getting error

[jwoo89]

Hi All,

Just wanted to say thank you for this forum as it has helped me greatly and has taught me a lot!!

I am having an issue with my nested if statement.

=IF(OR($H11="BOND",$H11="EQUITY",$H11="WARRANT"),($C11*$D11)/$E11,IF(OR($H11="FUTURE",$H11="SWAP"),INDEX(Current!T:T,MATCH($B11,Current!AX:AX,0),IF($H11="OPTION",(($C11*$D11)/$E11)*100),IF($H11="PIPE",(($C11*($D11-10))/$E11)))))

It works well until i get to the word SWAP, OPTION and PIPE. I am getting a error or a value error.

Does anyone know how to fix this?

Any help would be greatly appreciated!!

 

[Fluff]

You have some of the brackets in the wrong place, try

=IF(OR($H11="BOND",$H11="EQUITY",$H11="WARRANT"),($C11*$D11)/$E11,IF(OR($H11="FUTURE",$H11="SWAP"),INDEX(Current!T:T,MATCH($B11,Current!AX:AX,0)),IF($H11="OPTION",(($C11*$D11)/$E11)*100,IF($H11="PIPE",(($C11*($D11-10))/$E11)))))

 

[etaf]

=IF(

OR($H11="BOND",$H11="EQUITY",$H11="WARRANT"),

($C11*$D11)/$E11,

IF(OR($H11="FUTURE",$H11="SWAP"),

INDEX(Current!T:T,MATCH($B11,Current!AX:AX,0)
Missing a bracket above

,IF($H11="OPTION",

(($C11*$D11)/$E11)*100),

IF($H11="PIPE",
(($C11*($D11-10))/$E11 )

No FALSE condition - so if all above not met , then you get FALSE

))))

 

[jwoo89]

Thank you for that!

Is there anyway you would be able to provide me the actual formula? I tried to put in a bracket but i am still getting an error.

 

[etaf]

whats the error - i added the formula , as posted by FLUFF, and I get FALSE , so the formula syntax is OK, as i dont match any of the IF conditions hence it returns FALSE as expected

the error maybe from some of the equations you have or more likely the lookup , index/match

try just adding
=INDEX(Current!T:T,MATCH($B11,Current!AX:AX,0))
what do you get returned ?

can you post the spreadsheet via XL2BB ? see signature or menu
or put on a share ,
make sure NO personal/sensitive info, as this is a public forum available to all including searches via google

 

[jwoo89]

i can do that...

Still tried again and still can't get anything. Its just odd because i see the word option and it should not turn up as false as you can see above.

Thanks for your help!!

Book2.xlsx
	A	B	C	D	E	F	G	H	I
10	Symbol	Symbol	QTY	PX	FX	Value	notes	type	formula
11	ABC1	ABC1	10	2	1	20		EQUITY	20
12	ABC2	ABC2	11	2	1	22		OPTION	FALSE
13	ABC3	ABC3	12	2	1	24		WARRANT	24
14	ABC4	ABC4	13	2	1	26		PIPE	FALSE
15	ABC5	ABC5	14	2	1	28		SWAP	#VALUE!
16	ABC6	ABC6	15	2	1	30		BOND	30
17	ABC7	ABC7	16	2	1	32		FUTURE	#VALUE!
Sheet1

Cell Formulas
Range	Formula
F11:F17	F11	=C11*D11*E11
I11:I17	I11	=IF(OR($H11="BOND",$H11="EQUITY",$H11="WARRANT"),($C11*$D11)/$E11,IF(OR($H11="FUTURE",$H11="SWAP"),INDEX(DATA!T:T,MATCH($B11,DATA!AX:AX,0),IF($H11="OPTION",(($C11*$D11)/$E11)*100),IF($H11="PIPE",(($C11*($D11-10))/$E11)))))

Book2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY
1																				1																														ABC1
2																				2																														ABC2
3																				3																														ABC3
4																				4																														ABC4
5																				5																														ABC5
6																				6																														ABC6
7																				7																														ABC7
8
9
DATA

 

[Fluff]

You haven't change your formula, use the one I posted in post#2

 

[jwoo89]

i copied and pasted just now and still got the same errors in my example.

Changed the worksheet from CURRENT to DATA

Book2.xlsx
	A	B	C	D	E	F	G	H	I
10	Symbol	Symbol	QTY	PX	FX	Value	notes	type	formula
11	ABC1	ABC1	10	2	1	20		EQUITY	20
12	ABC2	ABC2	11	2	1	22		OPTION	FALSE
13	ABC3	ABC3	12	2	1	24		WARRANT	24
14	ABC4	ABC4	13	2	1	26		PIPE	FALSE
15	ABC5	ABC5	14	2	1	28		SWAP	#VALUE!
16	ABC6	ABC6	15	2	1	30		BOND	30
17	ABC7	ABC7	16	2	1	32		FUTURE	#VALUE!
18
Sheet1

Cell Formulas
Range	Formula
F11:F17	F11	=C11*D11*E11
I11:I17	I11	=IF(OR($H11="BOND",$H11="EQUITY",$H11="WARRANT"),($C11*$D11)/$E11,IF(OR($H11="FUTURE",$H11="SWAP"),INDEX(DATA!T:T,MATCH($B11,DATA!AX:AX,0),IF($H11="OPTION", (($C11*$D11)/$E11)*100),IF($H11="PIPE",(($C11*($D11-10))/$E11)))))

 

[Fluff]

That is not the formula from post#2

 

[jwoo89]

AHHH sorry its been a long day as you can tell

that was SOO helpful!!! really really appreciate it!!!!

can't thank you enough!!