I think I'm missing the obvious...

[Michelle Tucker]

=IF('All regions'!E32="*Brian*",'All regions'!E3,"1")
I don't know what I'm doing wrong! I've tried combinations of IF's and
ISNUMBER's and I can't do what I want!
What I want to happen is a formula which looks in a specific cell for
@Brian@ (but there may be two or more names e.g. Brian/Dave) and then returns
the date (which is in E3). If not, to return the value "1".
I thought I had it, but it wasn't right.
Also, if it does work, I want the formatting to be correct if it is a date,
or if it is just "1".
Is that making sense?
Again, I would be very grateful!

 

[Michelle Tucker]

Actually, can you strike that - I need to work backwards with a LOOKUP, which
again I'm not getting right!
The formula needs to look on a sheet for the same text as in a specific
cell. If it finds that text (it's actually a date) it needs to return the
name of a person in a specific cell. If it doesn't find the date, it needs to
return a specific number (at the moment, 1).
Clear?
It's not to me...

 

[Alan]

Try
=IF(ISNUMBER(FIND("Brian",'All regions'!E32)),'All regions'!E3,1)
FIND is case sensitive, if that's a problem use,
=IF(ISNUMBER(SEARCH("Brian",'All regions'!E32)),'All regions'!E3,1)
Regards,

 

[Sharad Naik]

If you want to saerch only in E32 then:
IF(ISERROR(SEARCH("Brian",E32)),1,E3)

If you want to multiple cells in a single column E (e.g. E1 to E32) then
IF(ISERROR(SEARCH("Brian",E1:E32)),1,E3)

But you can use as long as the data where to search in a single column
and not in multiple columns.

Sharad

or if you want to search in a range say for example