How to perform lookups based on multiple criteria?

B

Brandon

Hi all,

I'm having some troubles performing what I'm calling a compound
lookup. I've provided an example below. I have a City -> State
mapping, as well as a Name -> Sex mapping. Given the Name and City of
each conference attendee I'd like to determine how many are male and
from Texas.


The range A1:B10 contains a City -> State mapping.
City State
Allentown PA
Austin TX
Bristol PA
Camden NJ
Dallas TX
Hoboken NJ
Houston TX
Pittsburgh PA
Trenton NJ

The range D1:E7 contains a Name -> Sex mapping.
Name Sex
Bob M
Jennifer F
Mary F
Sally F
Sam M
Steve M

The range G1:H7 contains the Attendee -> City mapping.
Attendee City
Bob Austin
Jennifer Bristol
Jennifer Houston
Mary Dallas
Sam Camden
Steve Hoboken


I've tried this:
=SUMPRODUCT(VLOOKUP(G2:G7,D2:E7,2,FALSE)="M" *
VLOOKUP(H2:H7,A2:B10,2,FALSE)="TX")

But it seems to return #VALUE! regardless of whether I input it as an
array formula or not.


I've also tried:
=SUMPRODUCT(INDEX(A2:B10,MATCH(H2:H7,INDEX(A2:B10,,1),0),
2)="TX"*INDEX(D2:E7,MATCH(G2:G7,INDEX(D2:E7,,1),0),2)="M")

But that also returns #VALUE!


I'm not sure what I'm doing wrong here. Any other ideas of how I
could do this?


Thanks,
Brandon
 
A

Ashish Mathur

Hi,

Sorry this is the revised formula

SUMPRODUCT((LOOKUP(G2:G7,D2:D7,E2:E7)="M")*(LOOKUP(H2:H7,A2:B10,B2:B10)="TX"))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
M

Max

The range A1:B10 contains a City -> State mapping
The range D1:E7 contains a Name -> Sex mapping
The range G1:H7 contains the Attendee -> City mapping
Given the Name and City of each conference attendee
I'd like to determine how many are male and from Texas

Another play (all the same cell):
=SUMPRODUCT((T(OFFSET(B1,MATCH(H2:H7,A2:A10,0),))="TX")
*(T(OFFSET(E1,MATCH(G2:G7,D2:D7,0),))="M"))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---
 
S

ShaneDevenshire

Hi,

And yet another slightly shorter variation:

=SUMPRODUCT(--(LOOKUP(G2:G7,D2:E7)&LOOKUP(H2:H7,A2:B10)="MTX"))

If this is helpful, please click the Yes button.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top