If statement help

[Gaffnr]

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

 

[Stefi]

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:

 

[Gaffnr]

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")

 

[Roger Govier]

Hi

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

 

[Ron Rosenfeld]

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

 

[Gaffnr]

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

Insert>Name>Define> 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

 

[Gaffnr]

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

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

 

[Ron Rosenfeld]

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

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