VLookup Help

[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.

 

[SimonCC]

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.

 

[Mike]

=SUMIF(A1:C3,12345,C1:C3)
A B C
1 12345 2008 100,000
2 12345 2007 150,000
3 12345 2006 125,000

 

[pdberger]

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

 

[JRJ]

All great suggestions....thanks everyone!