Embedded if statement not returning the catch all if false

**nschneemann** · 03-02-2010, 12:46 PM

=IF(ISBLANK(A21),"-",IF(AND(Y21="-",Z21="-",AA21="-"),X21,IF(AND(X21="-",Y21="-",Z21="-"),AA21,IF(AND(X21="-",Y21="-",AA21="-"),Z21,IF(AND(X21="-",Z21="-",AA21="-"),Y21,"Custom Security Tool"))))) ***the above in cell AB21

This formula returns "--" TYPE reveals Excel thinks it is text and when I test in cell AC21-> AB21="--" it returns false. I have no idea what the catch all in the last IF statement is not dubbing it "Custom Security Tool", as I desire it to. Please help.

**6StringJazzer** · 03-02-2010, 01:13 PM

I don't see why

=AB21="--"

is returning FALSE, but your main problem is why the formula is returning "--". There is no way to diagnose this without seeing the workbook. Can you attach it?

**nschneemann** · 03-02-2010, 01:30 PM

Book2.xlsm

There are connections in there than feed to automated data from a secure source that will not be able to be transferred. Maybe there is enough there for you to help though. Thanks.

**darkyam** · 03-02-2010, 01:35 PM

nschneemann, welcome to the forum. Please take a moment to read the rules and then revise your title in compliance with rule #1. Thanks.

**darkyam** · 03-02-2010, 02:12 PM

I'm confused. It looks like the formula is doing what you tell it to. Could you please tell us in which cell or under which set of circumstances it is not working?

**nschneemann** · 03-02-2010, 03:46 PM

Item: 635405-10-3 - row 21on my information. In cell AC21 it is returning "--". I am having the "Generate Population" sheet index values from an unformatted output. What other information can I give you to help? Thank you for your time.

**darkyam** · 03-02-2010, 03:54 PM

Your first post says AB21 and gives a relatively short formula. AC21 is blank in what you provided, with a very long formula in AC1.

Assuming it was AB21, based on the formula you posted, I don't see the error. The part of the formula that says IF(AND(X21="-",Z21="-",AA21="-") returns TRUE, which means it returns Y21, which is --. What should it be showing? If you want it to show "Custom Security Tool", could you explain your logic step by step. Right now, unless at least two of the cells between X and AA read something other than -, you get the other cell.

**nschneemann** · 03-02-2010, 04:06 PM

Reading through your explanation I realized where the error was. I need to qualify what I am looking for earlier on in the string of equations. Thanks for your time, problem solved.