CODEWITHSUNDEEP
excelif-statementexcel-formulavlookup
JVMaverick
JVMaverick

Reputation: 69

EXCEL double IF statement with VLOOKUP - Handing Error on second IF statement

Basically, I have a double IF statement to check whether a certain value exists within the first list and if it's false then check a second list. Then if the value doesn't exists in the second list then I should know that the value doesn't exists in either list.

However, I'm having trouble with understanding in the particular scenario that the value doesn't exists in both list, why does my second false statement not work? I get #N/A for the value of 11.

I know I could use IFERROR, but I wanted to know the thought process of why it doesn't work!

enter image description here

Upvotes: 0

Views: 91

Answers (1)

BigBen
BigBen

Reputation: 50162

COUNTIF or COUNTIFS would be easier here:

=IF(COUNTIFS($D$2:$D$6,A2)+COUNTIFS($F$2:$F$17,A2)>0,"FOUND","NOT FOUND")

enter image description here

Or MATCH and ISNUMBER.

=IF(OR(ISNUMBER(MATCH(A2,$D$2:$D$6,0)),ISNUMBER(MATCH(A2,$F$2:$F$17,0))),"FOUND","NOT FOUND")

enter image description here

In your VLOOKUP approach, the first VLOOKUP failing is what causing the entire formula to return #N/A, as you can verify by using Formulas > Evaluate Formula. You would need to handle an error being thrown by either VLOOKUP. Nesting an IF doesn't circumvent this.

Upvotes: 2

Related Questions