trouble with lookup in a range of cells.

[evilthorne]

I am trying to get excel to look up in a range of cells I54:I102 to search
for a specific text "improved" and if true put a numerical value in another
cell. I can get it to work if I put it in the first cell but if I put it in
any other cell it returns with an error. What is the best formula to use in
this instance?

Thanks very much.

 

[T. Valko]

It's not real clear what you're trying to do.

Do you want to return a number from a cell that corresponds to some cell in
the range I54:I102 that contains improved?

Or, do you just wan't to know if improved is in the range I54:I102, and if
so then return "x".?

 

[evilthorne]

I am trying to have a number show up in cell o16 if the text "improved" comes
up in any of the cells between I54:I102 I have also named the I54:I102
"Status" in case this helps at all.

Thanks

 

[T. Valko]

Try this:

=IF(COUNTIF(Status,"improved"),10,"")

Replace 10 with whatever your number is.

 

[evilthorne]

Works great, thanks very much. I copy and pasted it from the site and it was
returning a false value so I cleared the contents and manually entered it and
eureka!!

 

[T. Valko]

You're welcome. Thanks for the feedback!