Hi Roger,
I've tried this but it fails....
Ive tried to understand your formula but its a bit alien to me...

InsertNameDefine Name myList Refers to =Sheet2!$A$1:$A$10

Done this and called it CompaniesList so following your formula, ive changed
it to read as per my ssheet.

=IF(C1="","",IF(ISNUMBER(SEARCH(C1,CompaniesList)) ,"do vlookup","do something
else"))


As I read the above, its saying, if C1 = "", put "". Thats cool
Next part is where I struggle.... I read it as
Then if not blank, = if C1 is a number, do vlookup, if not do something else?

Ive tried the ISNUMBER on its own and it returns false
Ive tried search and it returns an error.

Im at my wits end with this........ aaaaaaaaaaaarrrrggggggggg
Tks
Rob

As i read this, its sayi



--
Rob Gaffney


"Roger Govier" wrote:

Hi

Create a named range for your list.
InsertNameDefine Name myList Refers to =Sheet2!$A$1:$A$10
then
=IF(A1="","",IF(ISNUMBER(SEARCH(A1,myList)),"do vlookup","do something
else"))

--
Regards
Roger Govier



"Gaffnr" wrote in message
...
Thanks Stefi but it didnt work :-(

The search function is not doing its part I think.
My list of checks are on sheet 2, in cells A1 - A10 say.

So, I want my if statement on sheet 2 to do:

=if(C1 is in the list on Sheet!A1:A10,"then do vlookup","do something
else")

--
Rob Gaffney


"Stefi" wrote:

One solution (if your list is stored in a string):
=IF(ISERROR(SEARCH(A1,"123,456")),"do something else","do Vlookup")

Replace "do something else","do Vlookup" strings with the appropriate
functions!

Regards,
Stefi

"Gaffnr" ezt Ã*rta:

Hi All,
I need to write an if statement that says in non formula terms,

if CELL A1 = 123, do vlookup etc
or if CELL A1 = 456 do vlookup
or of CELL A1 = 789 do vlookup

I know I can do a repeated nested if, but when the list of what cell A1
could be is very long, my nested IF statement will become unmanageable.

What i want to do is a vlookup based upon the value in cell A1.
So, instead of nesting it, is there a way to say look at the value of
cell
A1 and =if it is in a seperate list, do the vlookup, if not do
something else.

So, if cell A1 (is in a list of ABC,DEF,GHI, JKL and so on) do the
vlookup,
if not do something else. Its gettign the IF to look to see if cell A1
is in
a list that I cant do.

I hope ive managed to explain myself.
Thanks
Rob