View original (French)

Non-estna

Solved
Fafa m -  
 fafa m -
Hello,

In a formula, one of my conditions is =IF(NOT(ISNA(VLOOKUP(A1;B1:C10;1;0)));0;1). Please, is there a counterpart to ISNA?

Thank you

Configuration: Windows XP / Internet Explorer 6.0

6 answers

  1.
    gbinforme Posted messages 14930 Registration date   Status Contributor Last intervention   4 744
     
    Hello Fafa m, the forum,

    To literally translate your request you can write:
    =IF("not NA"(VLOOKUP(A1,B1:C10,1,0));0;1)
    thus, 

    =IF(NOT(ISNA(VLOOKUP(A1,B1:C10,1,0)));0;1)

    --

    Always zen
    1
  2. fafa m
     
    I used the nb.si of Vaucluse and it works! Thank you all for your help.
    1
  3. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello
    I think it's better to work on the opposite:
    =IF(ISERROR(VLOOKUP(A1,B1:C10,2,0)),0,1)
    but curiously, this code does not return a value from C, but only 1 when the value in A1 exists in column B of the range!
    hence, maybe to simplify:
    =IF(COUNTIF(B1:C100,A1)=0,0,1)

    For my friend Lentz
    without wanting to quibble, I believe that the reverse formula you propose is exactly the same as that of the requester?

    best regards

    --
    Let’s ask ourselves if we are not the only ones to understand what is being explained?
    0
    1. Lentz
       
      I inverted the TRUE and FALSE values of the IF.
      0
    2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      exactly, I didn't see it!
      sorry dear friend.
      and since we're in conversation: why not sign up as a member? right?
      best regards
      0
    3. Lentz
       
      However, are you the same as the requester? Except for the column change?

      It's in progress ... but I don't have access to my inbox right now xD
      0
    4. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      No Lentz, I don't believe
      in yours as in that of the applicant, ""is not NA" in quotes assumes that the text is written in the cell.
      I thought (maybe wrongly, it's true, but given the title of the thread) that the applicant wanted to eliminate the #NA display that occurs when a formula doesn't find its criteria.
      The ISERROR or ISNA functions allow for this kind of manipulation.
      Best regards
      0
  4. Mike-31 Posted messages 18205 Registration date   Status Contributor Last intervention   5 147
     
    Hello,

    I apologize for the intrusion, but
    regardless of the formula, if this formula returns #N/A, it indicates that there is an error; either the value in A is not valid or it does not exist in the matrix B1:C10.
    VLOOKUP(A1, B1:C10, 1, 0)
    no matter how you manage this error, for example with IF(ISERROR(
    so, if VLOOKUP(A1, B1:C10, 1, 0) returns an error, we display 0; otherwise, we display 1, which is consistent with the formula
    =IF(ISERROR(VLOOKUP(A1, B1:C10, 1, 0)), 0, 1)

    unless we are trying to display the result of the search if it exists, or 0, or even nothing, or I didn't understand the request

    =IF(ISERROR(VLOOKUP(A1, B1:C10, 1, 0)), 0, VLOOKUP(A1, B1:C10, 2, 0))

    or
    =IF(ISERROR(VLOOKUP(A1, B1:C10, 1, 0)), "", VLOOKUP(A1, B1:C10, 2, 0))
    --
    Best,
    Mike-31

    A period of failure is a perfect time to sow the seeds of knowledge.
    0
  6. fafa m
     
    Thank you for all your responses. I poorly formulated my request; I will try to be more explicit:
    In column A, I have a list of 100 names
    In column B, I have a list of 10 names that can also be found in column A
    In column C, numbers related to each name in column B but not important for the formula
    My formula is in column D and already contains 6 conditions that should only apply if the name does not appear in column B. I am just looking to add a 7th condition that will prevent the application of the other 6 conditions as soon as the name in A appears in column B
    I provided this formula as an example because I simply thought that there was an inverse formula to ISNA
    I hope I have been clearer. My apologies.
    0
    1. Mike-31 Posted messages 18205 Registration date   Status Contributor Last intervention   5 147
       
      Can you copy your conditional formula from column D so we can see how to nest it?

      It would be best to have a part of your file without any confidential notes
      to attach using one of these links
      https://www.cjoint.com/ or http://www.cijoint.fr/
      0
    2. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      Hello,

      Use the nb.si() proposed by Vaucluse:
      =IF(COUNTIF(B1:B100,A1)>0,"A1 exists in B",other_tests)

      eric
      0
  7. Lentz
     
    I don't think so no

    But with the IF function you can reverse your condition,

    That is to say:

    =IF("is not NA"(VLOOKUP(A1,B1:C10,1,0)),1,0).
    -1