How to get "No" in if condition when the values is N/A

Question

I'm using this formula

=IF(VLOOKUP(A2,'sheet2'!A8:B353,1,FALSE),"Yes","No")

But when the VLOOKUP is not finding the value, I've a N/A in my cell with and an error message like did not find value. How to get a "no" in place of this Error message and N/A value ?

Answer 1

If you do not care about the result of the lookup, but just want to know whether the value in cell A2 appears in A8:A353, use one of these patterns:

=if( iserror( match(A2, 'sheet2'!A8:A353, 0) ), "No", "Yes" )

=if( iserror( vlookup(A2, 'sheet2'!A8:A353, 1, false) ), "No", "Yes" )

If you do want the result from the lookup, but also want to show "(no match)" when there is no match, you can "catch" the error with ifna() or iferror(), like this:

=iferror( filter('sheet2'!A8:A353, 'sheet2'!A8:A353 = A2) ), "(no match)" )

=ifna( vlookup(A2, 'sheet2'!A8:A353, 1, false), "(no match)" )

Answer 2

Instead of IF use IFNA

Relatad

• Use value from another cell in formula