IF isna to avoid #N/A

[Learning Excel]

I've been looking for this answer on previous posts but none found for my
formula : =vlookup($c$3,sheet2!$a$300:$d$400,columns($b:$e),0)
Where do I add the IF ISNA in this Vlookup formula, I tried in different
places
and "I missed a parentesis", or" too many arguments" or" else".

 

[Niek Otten]

=IF(ISNA(VLOOKUP($C$3,Sheet2!$A$300:$D$400,COLUMNS($B:$E),0)),"",VLOOKUP($C$3,Sheet2!$A$300:$D$400,COLUMNS($B:$E),0))

In general:

=IF(ISNA(YourFormula),"",YourFormula)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| I've been looking for this answer on previous posts but none found for my
| formula : =vlookup($c$3,sheet2!$a$300:$d$400,columns($b:$e),0)
| Where do I add the IF ISNA in this Vlookup formula, I tried in different
| places
| and "I missed a parentesis", or" too many arguments" or" else".
|
| --
| Socrates said: I only know, I don''''''''t know nothing.
| I say : I don''''''''t even know, I don''''''''t
| know nothing.

 

[Learning Excel]

Forgot 2 things
1- To return the cell empty ( no zeros)
2- Thanks in advance.

 

[T. Valko]

Try one of these:

There's no need to use columns($b:$e) as your column_index argument.

=IF(ISNA(MATCH($C$3,Sheet2!$A$300:$A$400,0)),"",VLOOKUP($C$3,Sheet2!$A$300:$D$400,4,0))

=IF(ISNA(VLOOKUP($C$3,Sheet2!$A$300:$D$400,4,0)),"",VLOOKUP($C$3,Sheet2!$A$300:$D$400,4,0))

=IF(COUNTIF(Sheet2!$A$300:$A$400,$C$3),VLOOKUP($C$3,Sheet2!$A$300:$D$400,4,0),"")

 

[Learning Excel]

Not just missing the If isna in my formula but the "" and the repetition of
the whole formula, no wonder I could not get it. Thanks a lot Niek Otten.
As for you T. Valko : Wonderformulas! Thanks guys.
--
Socrates said: I only know, I don''''''''t know nothing.
I say : I don''''''''t even know, I don''''''''t
know nothing.

Try one of these:

There's no need to use columns($b:$e) as your column_index argument.

=IF(ISNA(MATCH($C$3,Sheet2!$A$300:$A$400,0)),"",VLOOKUP($C$3,Sheet2!$A$300:$D$400,4,0))

=IF(ISNA(VLOOKUP($C$3,Sheet2!$A$300:$D$400,4,0)),"",VLOOKUP($C$3,Sheet2!$A$300:$D$400,4,0))

=IF(COUNTIF(Sheet2!$A$300:$A$400,$C$3),VLOOKUP($C$3,Sheet2!$A$300:$D$400,4,0),"")

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Not just missing the If isna in my formula but the "" and the repetition
of
the whole formula, no wonder I could not get it. Thanks a lot Niek Otten.
As for you T. Valko : Wonderformulas! Thanks guys.