Nested IF Statement not working

[TCerva]

Hi All - I have a nested IF statement that only partially works.

IF('20160229 ESD POS Import'!L3="B&H Foto and Electronics","005D0000003916X", IF('20160229 ESD POS Import'!L3='Special Accounts'!A:A,AY3,IF(L3<>'Special Accounts'!A:A,AV3)))

<colgroup><col width="114"></colgroup><tbody>
</tbody>

This formula works when L3 = "B&H Foto and Electronics",
it works when L3 is not found in 'Special Accounts'!A:A,
but it does not work when L3 is found in 'Special Accounts'!A:A.

When L3 is found in 'Special Accounts'!A:A, it should give me the value in cell AY3 but instead it is giving me the value in cell AV3.

I've tried other variations but each time only 2 out of the 3 conditions work:

IFERROR(IF(MATCH(L3,'temp special accts'!$L:$L,0),AY3,),AV3)

IF(AND(L3="B&H Foto AND Electronics"),"005D0000003916X",IF(ISNUMBER(SEARCH(L3,'Special Accounts'!A:A)),AY3,AV3))

IFERROR(IF(AND(L3="B&H Foto AND Electronics"),"005D0000003916X",IF('20160229 ESD POS Import'!L3='Special Accounts'!A:A,AY3,AV3)),AV3)

<colgroup><col></colgroup><tbody>
</tbody>

Sorry if this is written in a confusing way. Would someone be able to help me with this please?

Thank you!

 

[etaf]

try

IF('20160229 ESD POS Import'!L3="B&H Foto and Electronics","005D0000003916X",
IF('20160229 ESD POS Import'!L3='Special Accounts' AY3, AV3))

 

[TCerva]

@etaf - Thank you for your response! Tried your suggestion but excel says that there is an error. I was thinking that it was missing the "!A:A," so I plugged that in and it works again for 2 conditions, but not all three. It worked for when L3 was not found in 'Special Accounts' but it gave the wrong value for when L3 was found in 'Special Accounts'.

 

[etaf]

not sure what the !A:A is doing

whats the name of the sheet you are putting the formula into

this is the sheetname
'20160229 ESD POS Import'!
you are using to check if L3 on that sheet has the text
"B&H Foto and Electronics"
in

IF it does then you are putting
"005D0000003916X"
into the cell with the formula in

IF L3 does not = that text
then you are doing another IF
and now looking to see if the cell on that sheet has the text
'Special Accounts'

if it does ,then you are putting the value from AY3 for the sheet the formula is in into the cell the formula is in
If it does not = 'Special Accounts'
then you are putting AV3 into the cell

not sure why you are getting an error and need A:A

perhaps you could put a sample onto a share like dropbox or onedrive

 

[TCerva]

@etaf,

Apologies for the delay, I was doing quite a bit of traveling this week.

I figured out what I needed to do. Excel was not liking searching an entire column in a separate sheet for the list of Special Accounts, or at least as a 3rd condition in the formula. Since the list of Special Accounts is somewhat short I just plugged in a few IF(OR( statements and it seems to be working now.

Thank you for all your help!

Cheers,
Taylor