View original (French)

Check if an item is in a list

Cifer Posted messages 4 Status Membre -  
michel_m Posted messages 18903 Registration date   Status Contributeur 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 réponse

Réponse 1 / 1
michel_m Posted messages 18903 Registration date   Status Contributeur 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
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
Anonyme > Cifer
 
Hello,
The "reference list" needs to be sorted in ascending order.
Best regards
Audrey
0
michel_m Posted messages 18903 Registration date   Status Contributeur Last intervention   3 320 > Anonyme
 
Hello

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

Absolutely not!
0
michel_m Posted messages 18903 Registration date   Status Contributeur 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