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