IF Statement Range Question?

[Joycelet]

Hi

I have a range of cells 02:BK2 that could contain the word changed - if it is true then A2 = changed if false then do nothing?

Can i do a range in an if statement logical test?

Thanks in advance

N

 

[ThomasLafferty]

Hi!
Try this:

=IF(ISERROR(HLOOKUP("Changed",O2:BK2,1,FALSE)),"","Changed")

Tom

Born once die twice; born twice die once.

 

[swingkyd]

Rather than HLOOKUP you might want to use SEARCH. the help is actually "helpful" on it.

 

[ThomasLafferty]

Here's an explanation of the formula:

[!]HLOOKUP[/!]
look horizontally across the range O2:BK2 for the text "Changed" once the value has been located, look in row one of the range (in otherwords don't count down any since the range is one row in height) and return the value contained in that cell. The FALSE part of the argument means use exact match only. If the value does not exist, the #N/A error is generated.
[!]ISERROR[/!]
Evaluates to TRUE or FALSE
if the value "Changed" does not exist, then ISERROR = TRUE

"" means do nothing (true part) "Changed" means put the value in the cell.

Tom

Born once die twice; born twice die once.

 

[AnotherHiggins]

Or just:

[tab]=if(countif(O2:BK2,"changed")>0,"changed","")

If you would like to find if the word "changed" appeared anywhere within a cell, you could use:
[tab]=if(countif(O2:BK2,"*changed*")>0,"changed","")

That would return true for "asdfchangedasdf"

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Joycelet]

Hi

Thanks both for the reply will try and let you know how I get on

N