View original (French)

Check if an item is in a list

Cifer Posted messages 4 Status Member -  
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   -
Hello,

I would like to know if there is a formula that allows you to check if an item belongs to a list.

For example: I have a reference list on the first sheet (column A: the reference number, column B: the description of the reference), on a second sheet I have data where the reference number appears in column E).
I would like to check if the reference number from sheet 2 is indeed in the reference list on sheet 1.

I hope I have been clear in my explanations.

Thank you in advance.

Configuration: Windows 7 / Firefox 22.0

1 answer

  1.
    michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Good evening

    =COUNTIF(sheet1!A1:A100,test_reference)>0
    returns TRUE or FALSE
    or
    =(COUNTIF(sheet1!A1:A100,test_reference)>0)*1
    returns 1 or 0
    --
    Michel
    5
    1. Cifer
       
      Thank you for this response, I had tested this formula but it doesn’t work all the time. My references contain both letters and numbers. For example: DEQP003 is this important?
      1
      1. Anonyme > Cifer
         
        Hello,
        The "reference list" needs to be sorted in ascending order.
        Best regards
        Audrey
        0
      2. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320 > Anonyme
         
        Hello

        The "reference list" must be sorted in ascending order.

        Absolutely not!
        0
    2. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
       
      HELLO

      ....
      I tested this formula but it doesn't work all the time
      how about you tell us when it works and when it doesn't???
      0