Comparing a cell value to a range
 

Is there a function to see if the value of a cell appears in a range of cells?

I want to see if the value of cell A1 appears in any cell B1:B5. In other
words, if my sheet is such:
351 | 219
000 | 183
000 | 281
000 | 351 <-----
000 | 186
.... I should get a TRUE return.

Comparing a cell value to a range
 

Hi Paladin,

Try this,

=COUNTIF(B1:B5,A1)1

HTH
Regards,
Howard

"PaladinWhite" wrote in message
...
Is there a function to see if the value of a cell appears in a range of
cells?

I want to see if the value of cell A1 appears in any cell B1:B5. In other
words, if my sheet is such:
351 | 219
000 | 183
000 | 281
000 | 351 <-----
000 | 186
... I should get a TRUE return.

Comparing a cell value to a range
 

In say, C1: =IF(A1="","",ISNUMBER(MATCH(A1,B$1:B$5,0)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"PaladinWhite" wrote:
Is there a function to see if the value of a cell appears in a range of cells?

I want to see if the value of cell A1 appears in any cell B1:B5. In other
words, if my sheet is such:
351 | 219
000 | 183
000 | 281
000 | 351 <-----
000 | 186
... I should get a TRUE return.

Comparing a cell value to a range
 

Hi again Paladin,

Big woops, sorry. Try this:

=IF(COUNTIF(B1:B5,A1)0,"True","False")

HTH
Regards,
Howard

"PaladinWhite" wrote in message
...
Is there a function to see if the value of a cell appears in a range of
cells?

I want to see if the value of cell A1 appears in any cell B1:B5. In other
words, if my sheet is such:
351 | 219
000 | 183
000 | 281
000 | 351 <-----
000 | 186
... I should get a TRUE return.

Comparing a cell value to a range
 

=COUNTIF(B1:B5,A1)
The formula returns the number of times the value in A1 occurs in B1:B5
So, if you don't need the count, but just TRUE or FALSE then:
=IF(COUNTIF(B1:B5,A1),TRUE,FALSE)

Tyro

"PaladinWhite" wrote in message
...
Is there a function to see if the value of a cell appears in a range of
cells?

I want to see if the value of cell A1 appears in any cell B1:B5. In other
words, if my sheet is such:
351 | 219
000 | 183
000 | 281
000 | 351 <-----
000 | 186
... I should get a TRUE return.

Comparing a cell value to a range
 

another way:

=MATCH(A1;B1:B5,)
if you receive an error then there is no such value in B1:B5


=IF(MATCH(A1,B1:B5,),"True","")
if you receive an "empty" cell then tehere in such value in B1:B5


On 24 Mar, 00:45, PaladinWhite
wrote:
Is there a function to see if the value of a cell appears in a range of cells?

I want to see if the value of cell A1 appears in any cell B1:B5. In other
words, if my sheet is such:
351 | 219
000 | 183
000 | 281
000 | 351 <-----
000 | 186
... I should get a TRUE return.

Comparing a cell value to a range
 

another way:

=MATCH(A1;B1:B5,)
if you receive an error then there is no such value in B1:B5


=IF(MATCH(A1,B1:B5,),"True","")
if you receive an "empty" cell then tehere in such value in B1:B5



On 24 Mar, 00:45, PaladinWhite
wrote:
Is there a function to see if the value of a cell appears in a range of cells?

I want to see if the value of cell A1 appears in any cell B1:B5. In other
words, if my sheet is such:
351 | 219
000 | 183
000 | 281
000 | 351 <-----
000 | 186
... I should get a TRUE return.