View original (French)

VLOOKUP #N/A = 0

lolo35000 -  
Mike-31 Posted messages 18205 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

  1.
    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
  2. 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
    1. 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
  3. Mike-31 Posted messages 18205 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