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

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

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

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

On Thu, 27 Sep 2007 03:56:00 -0700, Gaffnr
wrote:

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

=if(countif(A1_lookup_list_range,A1)=0,"do something else",
vlookup(A1,table_array,col_index_num,[range_lookup]))

range_lookup is optional depending on whether your table_array is sorted by
lookup value.

--ron

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

Hi Ron
Thanks for this but it doesnt work either.
looks like ive found something that Excel cannot do.
rob
--
Rob Gaffney


"Ron Rosenfeld" wrote:

On Thu, 27 Sep 2007 03:56:00 -0700, Gaffnr
wrote:

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

=if(countif(A1_lookup_list_range,A1)=0,"do something else",
vlookup(A1,table_array,col_index_num,[range_lookup]))

range_lookup is optional depending on whether your table_array is sorted by
lookup value.

--ron

On Thu, 27 Sep 2007 06:32:02 -0700, Gaffnr
wrote:

Hi Ron
Thanks for this but it doesnt work either.
looks like ive found something that Excel cannot do.
rob
--
Rob Gaffney

I doubt that very much.

Either you have not explained your requirements clearly, or you are not
implementing properly the solutions that have been offered.

You will have a better chance if you provide specific data. But with what you
have provided, we can only guess at what might work.
--ron