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