VLOOKUP problem

[egildone]

I down a list of top 100 stock symbols to Excel and place them in Column
A and their rank in Column B (1-100). the next week I download a new
list of the top 100 stock symbols and their rank to Column C and D
respectively. then I create column E using the function
=VLOOKUP(C4,A:B,2,FALSE) Which tels me the rank each stock had last week
and if a stock is new this week it puts "#N/A" in the appropriate
cell in column E. How do I get the Vlookup function to put "NEW" in
column E if the is new to the list and wasn't in the list last week?

Ed

 

[Dave Peterson]

=if(isna(vlookup(...)),"NEW",vlookup(...))

xl2007 has a new =iferror() function:

=iferror(vlookup(...),"New")

 

[egildone]

Thanks Dave,that worked. Now in column D I have the rank this week and
column E has the rank last week except when a stock is new this week it
says NEW in Column E. Now I have created a column f with the formula:

=IF(D4=E4, "SAME",IF(D4<E4, "UP", IF(D4>E4, "DOWN",IF(E4="NEW","NEW"))))

but it doesn't put NEW in column F if it has NEW in column E, it puts UP
in column F when it says NEW in column E. How can I get NEW in columns
E and F if it has NEW in column E?

 

[Dave Peterson]

Maybe...

=IF(E4="new","new",IF(D4=E4,"same",IF(D4<E4,"up","down")))

 

[egildone]

Hey it worked! Thanks Dave

Ed

Maybe...

=IF(E4="new","new",IF(D4=E4,"same",IF(D4<E4,"up","down")))