Testing a range of cells in an IF FUNCTION

A

Aaron

Trying to test a range of cells in an IF Function. I would like the function to look at 15 cells in a single row, find a value within those cells, and then return another value if TRUE/FALSE. As of now, I can only apply the "logical test" in the IF formula for ONE CELL ONLY. I would like it to test a RANGE of cells in one row, find if any of the values match my logical test, and then return a value. Anyone understand or know how to do this? I tried apply "lookup" function, but I don't think it will work

Please help

Aaro
aaronplange at hotmail.com
 
F

Frank Kabel

Hi
maybe something like
=IF(COUNTIF(A1:A100,"your value")>1,"found","not found")

or
IF(ISNA(VLOOKUP("your_value",A1:B100,2,0)),"not
found",VLOOKUP("your_value",A1:B100,2,0))
 
R

Ron de Bruin

Hi Aaron

You can use the COUNTIF function

=IF(COUNTIF(A1:O1,"yourvalue")>0,"yes","No")

--
Regards Ron de Bruin
http://www.rondebruin.nl


Aaron said:
Trying to test a range of cells in an IF Function. I would like the function to look at 15 cells in a single row, find a value
Click to expand...
within those cells, and then return another value if TRUE/FALSE. As of now, I can only apply the "logical test" in the IF formula
for ONE CELL ONLY. I would like it to test a RANGE of cells in one row, find if any of the values match my logical test, and then
return a value. Anyone understand or know how to do this? I tried apply "lookup" function, but I don't think it will work.
 
B

Bob Phillips

Aaron,

Try something like

=NOT(ISNA(MATCH("user01",A2:H2,0)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Aaron said:
Trying to test a range of cells in an IF Function. I would like the
Click to expand...
function to look at 15 cells in a single row, find a value within those
cells, and then return another value if TRUE/FALSE. As of now, I can only
apply the "logical test" in the IF formula for ONE CELL ONLY. I would like
it to test a RANGE of cells in one row, find if any of the values match my
logical test, and then return a value. Anyone understand or know how to do
this? I tried apply "lookup" function, but I don't think it will work.
 
J

Jason Morin

Testing to see if 2 exists in the range A1:O1:

=IF(SUM(--(A1:O1=2))>0,TRUE,FALSE)

Array-entered, meaning press ctrl/shift/enter.

Although this is simpler:

=IF(COUNTIF(A1:O1,2),TRUE,FALSE)

HTH
Jason
Atlanta, GA
-----Original Message-----
Trying to test a range of cells in an IF Function. I
Click to expand...
would like the function to look at 15 cells in a single
row, find a value within those cells, and then return
another value if TRUE/FALSE. As of now, I can only apply
the "logical test" in the IF formula for ONE CELL ONLY. I
would like it to test a RANGE of cells in one row, find if
any of the values match my logical test, and then return a
value. Anyone understand or know how to do this? I tried
apply "lookup" function, but I don't think it will work.
 
Top