How to perform lookups based on multiple criteria?

[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

 

[Ashish Mathur]

Hi,

Try this.

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ashish Mathur]

Hi,

Sorry this is the revised formula

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[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,D27,0),))="M"))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,000 Files:362 Subscribers:62
xdemechanik
---

 

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