Excel Formula If Statement - multiple criteria

[jwoo89]

Hi All,

I have been trying to figure out the correct formula to use but I am getting stuck. Hopefully someone can help!

I have four columns A-D. In column A I need to pull in the correct sector from column B but Some cells i am not able to pull data in so the following words below show up. Basically if the below three texts show up in that cell, i need to move over to column C but that also appears to have the same issue and from there will need to move to column D and pull the sector from there.

#N/A Invalid Security

#N/A Field Not Applicable

#N/A N/A

So far I have the formula

=IF(M5="#N/A N/A",N5,IF(M5="#N/A Field Not Applicable",N5,IF(M5="N/A Invalid Security",N5,IF(N5="#N/A N/A",O5,IF(N5="#N/A Field Not Applicable",O5,IF(N5="N/A Invalid Security",O5,M5))))))

But when i get to the ones that are invalid and have either of those three words appear the If statement doesn't work.

Can somebody help?

Thanks for your help!!!

 

[Sufiyan97]

If that is the case then this works fine for me

=IF(NOT(OR(B2="#N/A N/A",B2="#N/A Field Not Applicable",B2="N/A Invalid Security")),B2,IF(NOT(OR(C2="#N/A N/A",C2="#N/A Field Not Applicable",C2="N/A Invalid Security")),C2,IF(NOT(OR(D2="#N/A N/A",D2="#N/A Field Not Applicable",D2="N/A Invalid Security")),D2,"Not in M, N or O")))

 

[jwoo89]

If that is the case then this works fine for me

=IF(NOT(OR(B2="#N/A N/A",B2="#N/A Field Not Applicable",B2="N/A Invalid Security")),B2,IF(NOT(OR(C2="#N/A N/A",C2="#N/A Field Not Applicable",C2="N/A Invalid Security")),C2,IF(NOT(OR(D2="#N/A N/A",D2="#N/A Field Not Applicable",D2="N/A Invalid Security")),D2,"Not in M, N or O")))

So it works well! but when the columns b and c have an error its not pulling in what is in column D for some reason

 

[Fluff]

Did you try the formula I suggested?

 

[jwoo89]

Did you try the formula I suggested?

yup i did! it doesn't work it is pulling in zeros =/

 

[Fluff]

It should work, I have included another option in col B

+Fluff 1.xlsm
	A	B	C	D	E
1
2	Electronics	Electronics	#N/A n/a	#N/A Field Not Applicable	Electronics
3	Electronics	Technology	Technology	#N/A Field Not Applicable	Electronics
4	Technology	Technology	#N/A Field Not Applicable	Technology
List

Cell Formulas
Range	Formula
A2:A4	A2	=LOOKUP(2,1/((LEFT(C2:E2,4)<>"#N/A")*(C2:E2<>"")),C2:E2)
B2:B4	B2	=INDEX(C2:E2,AGGREGATE(15,6,(COLUMN(C2:E2)-COLUMN(C2)+1)/(LEFT(C2:E2,4)<>"#N/A"),1))

 

[Sufiyan97]

So it works well! but when the columns b and c have an error its not pulling in what is in column D for some reason

here it works perfect

Book5
	A	B	C	D
1	A	B	C	D
2	2	#N/A Field Not Applicable	#N/A Field Not Applicable	2
Sheet1

Cell Formulas
Range	Formula
A2	A2	=IF(NOT(OR(B2="#N/A N/A",B2="#N/A Field Not Applicable",B2="N/A Invalid Security")),B2,IF(NOT(OR(C2="#N/A N/A",C2="#N/A Field Not Applicable",C2="N/A Invalid Security")),C2,IF(NOT(OR(D2="#N/A N/A",D2="#N/A Field Not Applicable",D2="N/A Invalid Security")),D2,"Not in M, N or O")))

 

[Sufiyan97]

All works fine except A3

Book6
	A	B	C	D	E	F
1	Fluff	Fluff	Ali
2	Electronics	Electronics	Electronics	#N/A n/a	#N/A Field Not Applicable	Electronics
3	Electronics	Technology	Technology	Technology	#N/A Field Not Applicable	Electronics
4	Technology	Technology	Technology	#N/A Field Not Applicable	Technology
Sheet1

Cell Formulas
Range	Formula
A2:A4	A2	=LOOKUP(2,1/((LEFT(D2:F2,4)<>"#N/A")*(D2:F2<>"")),D2:F2)
B2:B4	B2	=INDEX(D2:F2,AGGREGATE(15,6,(COLUMN(D2:F2)-COLUMN(D2)+1)/(LEFT(D2:F2,4)<>"#N/A"),1))
C2:C4	C2	=IF(NOT(OR(D2="#N/A N/A",D2="#N/A Field Not Applicable",D2="N/A Invalid Security")),D2,IF(NOT(OR(E2="#N/A N/A",E2="#N/A Field Not Applicable",E2="N/A Invalid Security")),E2,IF(NOT(OR(F2="#N/A N/A",F2="#N/A Field Not Applicable",F2="N/A Invalid Security")),F2,"Not in Any of three")))

 

[jwoo89]

here it works perfect

Book5
	A	B	C	D
1	A	B	C	D
2	2	#N/A Field Not Applicable	#N/A Field Not Applicable	2
Sheet1

Cell Formulas
Range	Formula
A2	A2	=IF(NOT(OR(B2="#N/A N/A",B2="#N/A Field Not Applicable",B2="N/A Invalid Security")),B2,IF(NOT(OR(C2="#N/A N/A",C2="#N/A Field Not Applicable",C2="N/A Invalid Security")),C2,IF(NOT(OR(D2="#N/A N/A",D2="#N/A Field Not Applicable",D2="N/A Invalid Security")),D2,"Not in M, N or O")))

AHHH THIS IS AMAZING!!! thank you so much! it worked!!!

 

[jwoo89]

It should work, I have included another option in col B

+Fluff 1.xlsm
	A	B	C	D	E
1
2	Electronics	Electronics	#N/A n/a	#N/A Field Not Applicable	Electronics
3	Electronics	Technology	Technology	#N/A Field Not Applicable	Electronics
4	Technology	Technology	#N/A Field Not Applicable	Technology
List

Cell Formulas
Range	Formula
A2:A4	A2	=LOOKUP(2,1/((LEFT(C2:E2,4)<>"#N/A")*(C2:E2<>"")),C2:E2)
B2:B4	B2	=INDEX(C2:E2,AGGREGATE(15,6,(COLUMN(C2:E2)-COLUMN(C2)+1)/(LEFT(C2:E2,4)<>"#N/A"),1))

This also worked!! thank you both so much fluff and A durfani!!! you guys are awesome

 

[Fluff]

Glad we could help & thanks for the feedback.