VLookup Help

  • Thread starter Thread starter JRJ
  • Start date Start date
J

JRJ

Hello,
I am using vlookup to return the "Sales" for a particular "Location"
depending on the Yr. My result is always the sales for the first year, which
I assumed is what would happen. Is there any way around this?

Location Yr Sales
12345 2008 100,000
12345 2007 150,000
12345 2006 125,000

Thanks for the help.
 
Hi,

Assuming your original lookup range is columns A through C, insert a new
column between Yr column and Sales column. You Sales column would be pushed
to column D. In the new column C, starting from cell C2, use the formula
=A2&B2
and copy the formula down however far is necessary.
Now your new lookup range will be columns C and D. If your lookup value
contains the combination of Location and Yr, it should return the correct
Sales value.
 
=SUMIF(A1:C3,12345,C1:C3)
A B C
1 12345 2008 100,000
2 12345 2007 150,000
3 12345 2006 125,000
 
JRJ --

As I understand it, you want to find (for example) the sales in location
12345 for year 2007. If so, here's an approach:

=SUMPRODUCT(--(A1:A5=12345),--(B1:B5=2007),C1:C5)

Should do the trick.

HTH
 
Back
Top