return 1 if value found in list

[MICMERG]

Hi,

I have a long single column list and need to see if a value appears in the list.

So, if my list goes from A1 to A999 and I am looking to see if number 51 (value of cell D43) appears in list. If number 51 is there, return 1 if not, return 0

Thx

 

[Aladin Akyurek]

Hi,

I have a long single column list and need to see if a value appears in the list.

So, if my list goes from A1 to A999 and I am looking to see if number 51 appears in list. If number 51 is there, return 1 if not, return 0

Thx

Either...

=ISNUMBER(MATCH(51,$A$1:$A$999,0))+0

Or...

=ISNUMBER(MATCH(E1,$A$1:$A$999,0))+0

where E1 houses a value like 51.

 

[T. Valko]

Hi,

I have a long single column list and need to see if a value appears in the list.

So, if my list goes from A1 to A999 and I am looking to see if number 51 (value of cell D43) appears in list. If number 51 is there, return 1 if not, return 0

Thx

Try this...

=--(COUNTIF(A1:A999,D43)>0)

 

[MICMERG]

Valko. Doesn't work

 

[MICMERG]

Aladin

Don't know why but works great when values in array go from 1-20 but if I change from 21-49 I get all 1s

 

[T. Valko]

Valko. Doesn't work

Book1
	A
1	27
2	43
3	39
4	15
5	51
6	23
7	0
8	75
9	38
10	16
Sheet1

D43 = 51

=--(COUNTIF(A1:A999,D43)>0)

Returns 1

If that formula doesn't work then you have data problems.

 

[MICMERG]

You are correct - My bad.

Even works when looking for 2 values on another sheet: =--AND((COUNTIF(RESULTATS!$C$5:$V$5;$C$1)>0);(COUNTIF(RESULTATS!$C$5:$V$5;B3)>0))

Guess that's what 20 hrs of Excel and Access will do to you.

Thx for the help.

 

[Aladin Akyurek]

Aladin

Don't know why but works great when values in array go from 1-20 but if I change from 21-49 I get all 1s

You probably have text numbers in the range of interest. By the way, IsNumber/Match is faster than CountIf...

 

[T. Valko]

You are correct - My bad.

Even works when looking for 2 values on another sheet: =--AND((COUNTIF(RESULTATS!$C$5:$V$5;$C$1)>0);(COUNTIF(RESULTATS!$C$5:$V$5;B3)>0))

Guess that's what 20 hrs of Excel and Access will do to you.

Thx for the help.

You're welcome. Thanks for the feedback!

 

[T. Valko]

either...

=isnumber(match(51,$a$1:$a$999,0))+0

or...

=isnumber(match(e1,$a$1:$a$999,0))+0

where e1 houses a value like 51.

=count(match(51,a$1:a$999,0))