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]

Try

=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,"#N/A N/A"))))))

 

[jwoo89]

Thanks for your quick response! but sadly it didn't work.

What i want to return in column A is below highlighted in yellow. Basically Column A needs to pull in the sector. But if Columns B and C have either #N/A invalid Security, #N/A Field not Applicable or #N/A N/A i want it to look to the next cell where it will have an actual sector.

=IF(M4="#N/A N/A",N4,IF(M4="#N/A Field Not Applicable",N4,IF(M4="N/A Invalid Security",N4,IF(N4="#N/A N/A",O4,IF(N4="#N/A Field Not Applicable",O4,IF(N4="N/A Invalid Security",O4,M4))))))

 

[Sufiyan97]

Try

=IF(NOT(OR(M2="#N/A N/A",M2="#N/A Field Not Applicable",M2="N/A Invalid Security")),M2,IF(NOT(OR(N2="#N/A N/A",N2="#N/A Field Not Applicable",N2="N/A Invalid Security")),N2,IF(NOT(OR(O2="#N/A N/A",O2="#N/A Field Not Applicable",O2="N/A Invalid Security")),O2,"Not in M, N or O")))

 

[Fluff]

Can you have 2 columns that return a valid sector? if so which should be used?

 

[Fluff]

If there is only one valid sector, then you could use

=LOOKUP(2,1/((LEFT(B2:D2,4)<>"#N/A")*(B2:D2<>"")),B2:D2)

 

[jwoo89]

Try

=IF(NOT(OR(M2="#N/A N/A",M2="#N/A Field Not Applicable",M2="N/A Invalid Security")),M2,IF(NOT(OR(N2="#N/A N/A",N2="#N/A Field Not Applicable",N2="N/A Invalid Security")),N2,IF(NOT(OR(O2="#N/A N/A",O2="#N/A Field Not Applicable",O2="N/A Invalid Security")),O2,"Not in M, N or O")))

ah still didn't work thank you for trying!

 

[jwoo89]

Can you have 2 columns that return a valid sector? if so which should be used?

Yup that is possible! If there is something in the second column we will use that but if there appears to be any of those three errors i want to go to the next cell but that can either have a sector pulled in or it could also have any of those three errors in which case would go to the last cell in column d.

 

[Sufiyan97]

if sector in column B2 then return B2
If B2 has error then go to C2
if sector in column C2 then return C2
If C2 has error then go to D2
if sector in column D2 then return D2
If D2 has error then no result

Correct?

 

[jwoo89]

if sector in column B2 then return B2
If B2 has error then go to C2
if sector in column C2 then return C2
If C2 has error then go to D2
if sector in column D2 then return D2
If D2 has error then no result

Correct?

Yup that is correct! Any of those three errors can appear in that field