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