IF(ISERROR with multiple if conditions or combine two IF(ISERROR

[AlineCastiVitkov]

How can I combine the formulas into ONE? I'm having a hard time the order in which they should be, I either get the "missing parenthesis error" or the "too few arguments" screen.
=IF(ISERROR(FIND("X",Q11)), VLOOKUP(H11,$AE$1:$AH$6,2,FALSE),"")
=IF(ISERROR(FIND("HON",B11)), VLOOKUP(H11,$AE$1:$AH$6,3,FALSE),VLOOKUP(H11,$AE$1:$AH$6,4,FALSE))
I need to incorporate the condicton IF(FIND("AP",B11)), in order for VLOOKUP(H11,$AE$1:$AH$6,4,FALSE)

Thank you

 

[etaf]

FIND is case sensitive
=ISNUMBER(SEARCH("x",Q11))

BUT

IF(FIND("AP",B11)), in order for VLOOKUP(H11,$AE$1:$AH$6,4,FALSE)

Needs the ISERROR
IF(ISERROR(FIND("AP",B11)), in order for VLOOKUP(H11,$AE$1:$AH$6,4,FALSE)

=IF(ISERROR(FIND("X",Q11)),VLOOKUP(H11,$AE$1:$AH$6,2,FALSE),IF(ISERROR(FIND("HON",B11)),VLOOKUP(H11,$AE$1:$AH$6,3,FALSE),IF(ISERROR(FIND("AP",B11)),VLOOKUP(H11,$AE$1:$AH$6,4,FALSE),"")))

=IF(ISERROR(FIND("X",Q11)), VLOOKUP(H11,$AE$1:$AH$6,2,FALSE),  IF(ISERROR(FIND("HON",B11)), VLOOKUP(H11,$AE$1:$AH$6,3,FALSE),IF(ISERROR(FIND("AP",B11)),VLOOKUP(H11,$AE$1:$AH$6,4,FALSE),"")))

you may want to wrap in an IFERROR if the vlookup's are not found to avoid an N/A error

IFERROR  (  IF(ISERROR(FIND("X",Q11)), VLOOKUP(H11,$AE$1:$AH$6,2,FALSE),  IF(ISERROR(FIND("HON",B11)), VLOOKUP(H11,$AE$1:$AH$6,3,FALSE),IF(ISERROR(FIND("AP",B11)),VLOOKUP(H11,$AE$1:$AH$6,4,FALSE),""))),"")

 

[AlineCastiVitkov]

FIND is case sensitive
=ISNUMBER(SEARCH("x",Q11))

BUT

Needs the ISERROR
IF(ISERROR(FIND("AP",B11)), in order for VLOOKUP(H11,$AE$1:$AH$6,4,FALSE)

=IF(ISERROR(FIND("X",Q11)),VLOOKUP(H11,$AE$1:$AH$6,2,FALSE),IF(ISERROR(FIND("HON",B11)),VLOOKUP(H11,$AE$1:$AH$6,3,FALSE),IF(ISERROR(FIND("AP",B11)),VLOOKUP(H11,$AE$1:$AH$6,4,FALSE),"")))

=IF(ISERROR(FIND("X",Q11)), VLOOKUP(H11,$AE$1:$AH$6,2,FALSE),  IF(ISERROR(FIND("HON",B11)), VLOOKUP(H11,$AE$1:$AH$6,3,FALSE),IF(ISERROR(FIND("AP",B11)),VLOOKUP(H11,$AE$1:$AH$6,4,FALSE),"")))

you may want to wrap in an IFERROR if the vlookup's are not found to avoid an N/A error

IFERROR  (  IF(ISERROR(FIND("X",Q11)), VLOOKUP(H11,$AE$1:$AH$6,2,FALSE),  IF(ISERROR(FIND("HON",B11)), VLOOKUP(H11,$AE$1:$AH$6,3,FALSE),IF(ISERROR(FIND("AP",B11)),VLOOKUP(H11,$AE$1:$AH$6,4,FALSE),""))),"")

FIND is case sensitive
=ISNUMBER(SEARCH("x",Q11))

BUT

Needs the ISERROR
IF(ISERROR(FIND("AP",B11)), in order for VLOOKUP(H11,$AE$1:$AH$6,4,FALSE)

=IF(ISERROR(FIND("X",Q11)),VLOOKUP(H11,$AE$1:$AH$6,2,FALSE),IF(ISERROR(FIND("HON",B11)),VLOOKUP(H11,$AE$1:$AH$6,3,FALSE),IF(ISERROR(FIND("AP",B11)),VLOOKUP(H11,$AE$1:$AH$6,4,FALSE),"")))

=IF(ISERROR(FIND("X",Q11)), VLOOKUP(H11,$AE$1:$AH$6,2,FALSE),  IF(ISERROR(FIND("HON",B11)), VLOOKUP(H11,$AE$1:$AH$6,3,FALSE),IF(ISERROR(FIND("AP",B11)),VLOOKUP(H11,$AE$1:$AH$6,4,FALSE),"")))

you may want to wrap in an IFERROR if the vlookup's are not found to avoid an N/A error

IFERROR  (  IF(ISERROR(FIND("X",Q11)), VLOOKUP(H11,$AE$1:$AH$6,2,FALSE),  IF(ISERROR(FIND("HON",B11)), VLOOKUP(H11,$AE$1:$AH$6,3,FALSE),IF(ISERROR(FIND("AP",B11)),VLOOKUP(H11,$AE$1:$AH$6,4,FALSE),""))),"")

Hello, thank you for the suggestions, although I get no errors, the formula is not working properly as when there is an "X" in cell Q11 the result should be an empty cell, instead, it is giving the result of the condition of looking in table (AE1:AH6) when cell B11 contains "AP".

 

[Alex Blakenburg]

Your use of Find implies you don't want ="X" but contains "X" etc. Let us know if that is not the case.

I suspect your logic is back to front.
Assuming your want
Test X then column 2
if not then test HON then column 3
if not then test AP then column 4
otherwise ""

Then this should work for you.

=IF(COUNTIFS(Q11, "*X*"),
         VLOOKUP(H11, $AE$1:$AH$6, 2, FALSE),
         IF(COUNTIFS(B11, "*HON*"),
                VLOOKUP(H11, $AE$1:$AH$6, 3, FALSE),
               IF(COUNTIFS(B11, "*AP*"),
                      VLOOKUP(H11, $AE$1:$AH$6, 4, FALSE),
                      "")))

 

[etaf]

we have 3 lookups
IFERROR ( IF(ISERROR(FIND("X",Q11)), VLOOKUP(H11,$AE$1:$AH$6,2,FALSE), IF(ISERROR(FIND("HON",B11)), VLOOKUP(H11,$AE$1:$AH$6,3,FALSE),IF(ISERROR(FIND("AP",B11)),VLOOKUP(H11,$AE$1:$AH$6,4,FALSE),""))),"")
Q11, and 2 for B11
so as B11 cannot be AP and HON at the same time - then its going to return a TRUE and do a lookup
and if value not found return an N/A

you could add an additional IF
=AND(ISERROR(FIND("X",Q11)),ISERROR(FIND("HON",B11)),ISERROR(FIND("AP",B11)))
which will return "" if cells do NOT contain X, AP or HON

=IF(AND(ISERROR(FIND("X",Q11)),ISERROR(FIND("HON",B11)),ISERROR(FIND("AP",B11))), "",
=IF(ISERROR(FIND("X",Q11)), VLOOKUP(H11,$AE$1:$AH$6,2,FALSE), IF(ISERROR(FIND("HON",B11)), VLOOKUP(H11,$AE$1:$AH$6,3,FALSE),IF(ISERROR(FIND("AP",B11)),VLOOKUP(H11,$AE$1:$AH$6,4,FALSE),""))))

 

[AlineCastiVitkov]

To clarify, the logic needed is:
If “X” is found in Q11, then cell = Empty
If “X” is not found then, analyze for “HON” in cell B11, If “HON” is found then look for value of H11 in 3rd column of table AE1::AH6.
If “HON” is not found then, analyze for “AP” in cell B11, If “AP” is found then look for value of H11 in 4th column of table AE1::AH6.
If “AP” is not found then look for value of H11 in 2nd column of table AE1::AH6.
If error then cell = empty.

Thank You all for your help

 

[Alex Blakenburg]

Try

=IF(COUNTIFS(Q11, "*X*"),
         "",
         IF(COUNTIFS(B11, "*HON*"),
                VLOOKUP(H11, $AE$1:$AH$6, 3, FALSE),
               IF(COUNTIFS(B11, "*AP*"),
                      VLOOKUP(H11, $AE$1:$AH$6, 4, FALSE),
                    VLOOKUP(H11, $AE$1:$AH$6, 2, FALSE)  )))

 

[AlineCastiVitkov]

Try

=IF(COUNTIFS(Q11, "*X*"),
         "",
         IF(COUNTIFS(B11, "*HON*"),
                VLOOKUP(H11, $AE$1:$AH$6, 3, FALSE),
               IF(COUNTIFS(B11, "*AP*"),
                      VLOOKUP(H11, $AE$1:$AH$6, 4, FALSE),
                    VLOOKUP(H11, $AE$1:$AH$6, 2, FALSE)  )))

Thank You for the code above, it worked.