View original (French)

VLOOKUP #N/A = 0

lolo35000 -  
Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   -
Hello,

I performed a search function that works.
However, logically it returns #N/A values.
I would like these values to be equal to zero.
How can I do this?

Thank you in advance.

Configuration: iPhone / Safari 530.17

3 answers

Answer 1 / 3
chossette9 Posted messages 6855 Registration date   Status Contributor Last intervention   1 313
 
Hello,

if you get #N/A and you want to display 0 instead, try this: 
=IF(ISERROR(VLOOKUP(...));0;VLOOKUP(...))
Best regards.
--
This guy wasn't the brightest, as he was naive.
0
Answer 2 / 3
lolo35000
 
Thank you for your quick response!!
With the ESTERR formula, I still had NAs.
So I tried with the ESTERREUR formula and it works

Thanks again
0
chossette9 Posted messages 6855 Registration date   Status Contributor Last intervention   1 313
 
Indeed, I went a bit too fast. ESTERR() allows you to determine formula errors EXCEPT for #N/A.

Sorry for the slight setback.

Best regards.
0
Answer 3 / 3
Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
 
Hello,

The #N/A message appears when the search criterion is not provided.

=IF(ISERROR(VLOOKUP(your criterion; your search range; the column number of the searched criterion; 0));"";VLOOKUP(your criterion; your search range; the column number of the searched criterion; 0))

which should translate clearly (for example, the criterion in G2 and the matrix A1:F50 to display the result from column 2, which is B)

=IF(ISERROR(VLOOKUP(G2;A1:F50;2;0));"";VLOOKUP(G2;A1:F50;2;0))

If your search matrix is on another sheet as well as on the same sheet, I recommend naming the search range like A1:F50 named List
which will shorten your formula.

=IF(ISERROR(VLOOKUP(G2;List;2;0));"";VLOOKUP(G2;List;2;0))

--
Best,
Mike-31

A period of failure is an ideal moment to sow the seeds of knowledge.
0