Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing to a range and printing value | Excel Discussion (Misc queries) | |||
comparing one to one cell within range | Excel Worksheet Functions | |||
Comparing One Range and Adding Another | Excel Worksheet Functions | |||
Comparing a Range of Values | Excel Discussion (Misc queries) | |||
Comparing a cell result with a pre-defined value range | Excel Worksheet Functions |