vlookup with 2 sets of criteria

[Guest]

I'm trying to do a vlookup with 2 sets of criteria. For example: I want to
look up a customer region ( North, South, Central, West) in column A and the
customer number in column D, if they both match then bring back the sales $
in column F.

My issue is that the customer numbers sell into multiple regions across the
country so they are listed multiple times down the column and there are
clearly multiple customer numbers under each region.

Thank you for your help!!!

David

 

[Pete_UK]

You could concatenate the Region and Customer Number in a helper
column to give you a unique combination, but vlookup will only return
the first match it finds - I think you want to sum all the sales
figures for a particular Region and Customer Number. If this is the
case, then try this:

Use a cell to enter the Region (eg J2) and another cell to enter the
customer number (eg K2) and enter this formula in L2:

=SUMPRODUCT((A$2:A$100=J2)*(D$2$100=K2)*(F$2:F$100))

This will sum all the sales from column F which match the Region in J2
and the Customer Number in K2. I've assumed your data covers from row
2 to row 100 - adjust as necessary.

If you want to enter other Regions in column J and other Customer
Numbers in column K, you can copy this formula down column L to cater
for this.

Hope this helps.

Pete

 

[Stan Brown]

Fri, 8 Jun 2007 06:03:02 -0700 from laf2day

I'm trying to do a vlookup with 2 sets of criteria. For example: I want to
look up a customer region ( North, South, Central, West) in column A and the
customer number in column D, if they both match then bring back the sales $
in column F.

I may be wrong, but I don't think you can do this with VLOOKUP.
Instead you have to use a database function with a criterion matrix
of two columns.