Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to perform lookups based on multiple criteria?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to perform lookups based on multiple criteria?
Hi,
Try this. SUMPRODUCT((LOOKUP(G2:G7,D2:D7,E2:E7)="M")*(LOOKUP (H2:H7,A2:B10,B2:B10)="TX"),I2:I7) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Brandon" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to perform lookups based on multiple criteria?
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 "Brandon" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to perform lookups based on multiple criteria?
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 --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to perform lookups based on multiple criteria?
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. -- Thanks, Shane Devenshire "Brandon" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count based on multiple criteria | Excel Worksheet Functions | |||
sum based on multiple criteria | Excel Worksheet Functions | |||
How do you perform lookups when the info is always moving? | New Users to Excel | |||
Summary Based on Multiple Criteria | Excel Worksheet Functions | |||
Multiple Sum, based on criteria | Excel Worksheet Functions |