vlookup

[jhnewyork]

I have revenue numbers for 2007 and 2008 in columns, I need to do a vlookup
(i think) that will classify into four columns demarking wih a check mark
whether column 1 - the revenue increased, column 2 - revenue was new for
2008, column 3 - decreased and column 4 -if it decreased whetheth the
business went away or sales were lower in 2008. I'm fustrated because I know
what I want to do and know it's possible, but can't figure out what the
formula is - help!

 

[Joel]

Do 4 vlokkups to return a different column for each lookup.

 

[JLatham]

I'm guessing that you have 2 sheets. Here are my 'assumptions' for my
solution:

2 sheets, named 2007Rev and 2008Rev
Unique revenue identifier in column A, amt of revenue in column B on both.
Data begins on row 2 of each sheet, and on 2007Rev it goes down to row 200
All other columns available, so we start in column C

in C2, just to make it easy with shorter formulas here, we bring over the
revenue from 2007Rev into the 2008Rev sheet
=IF(ISNA(VLOOKUP(A2,'2007Rev'!A$2:B$200,2,FALSE)),0,VLOOKUP(A2,'2007Rev'!A$2:B$200,2,FALSE))

In D2 we find out if revenue increased, decreased or remained the same
=IF(B2>C2,"Increased",IF(B2<C2,"Decreased","No Change"))

in E2 we see if it looks like new business (not listed in 2007Rev sheet)
=IF(ISNA(VLOOKUP(A2,'2007Rev'!A$2:B$200,2,FALSE)),"New for 2008","")

and in F2 we see if decreased revenue is just from reduced sales or if it
looks like the account was lost (0 revenue for 2008)
=IF(B2>C2,"",IF(AND(B2<C2,B2=0),"Lost Account?",IF(B2>0,"Reduced Sales","")))

Hope that helps some.

 

[jhnewyork]

thak you!
--
Thanks,
Jennifer

Do 4 vlokkups to return a different column for each lookup.

 

[jhnewyork]

you're the best - thank you!
--
Thanks,
Jennifer

I'm guessing that you have 2 sheets. Here are my 'assumptions' for my
solution:

2 sheets, named 2007Rev and 2008Rev
Unique revenue identifier in column A, amt of revenue in column B on both.
Data begins on row 2 of each sheet, and on 2007Rev it goes down to row 200
All other columns available, so we start in column C

in C2, just to make it easy with shorter formulas here, we bring over the
revenue from 2007Rev into the 2008Rev sheet:
=IF(ISNA(VLOOKUP(A2,'2007Rev'!A$2:B$200,2,FALSE)),0,VLOOKUP(A2,'2007Rev'!A$2:B$200,2,FALSE))

In D2 we find out if revenue increased, decreased or remained the same
=IF(B2>C2,"Increased",IF(B2<C2,"Decreased","No Change"))

in E2 we see if it looks like new business (not listed in 2007Rev sheet)
=IF(ISNA(VLOOKUP(A2,'2007Rev'!A$2:B$200,2,FALSE)),"New for 2008","")

and in F2 we see if decreased revenue is just from reduced sales or if it
looks like the account was lost (0 revenue for 2008)
=IF(B2>C2,"",IF(AND(B2<C2,B2=0),"Lost Account?",IF(B2>0,"Reduced Sales","")))

Hope that helps some.