| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup for 2 sets of Criteria (or do I need to use something else
I'm trying to do a vlookup with 2 sets of criteria. For example: I want to
look up a customer name in column A and the customer state in column D, if they both match then bring back the # of employees per state in column F. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup for 2 sets of Criteria (or do I need to use something else
=INDEX(F1:F100,MATCH(1,(A1:A100="Custname")*(D1:D1 00="State"),0))
Replace Custname and State by text or Cells containing your values Enter with Ctrl+Shift+Enter OR =SUMPRODUCT(--(A1:A100="Custname"),--(D1:D100="State"),F1:F100) just Enter. "Buzz07" wrote: I'm trying to do a vlookup with 2 sets of criteria. For example: I want to look up a customer name in column A and the customer state in column D, if they both match then bring back the # of employees per state in column F. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup for 2 sets of Criteria (or do I need to use something
This is definately got me headed in the right direction. I should provide
more info... I have a Grid in Sheet 1 that I am populating with information from a table in Sheet 2. Sheet 1 Verticle Headers in the grid = Customers Horizontal Headers in the grid = State Table in Sheet 2 Verical = State Vertical = Client Full Name Vertical = CountOfParticipant SSN Were sheet 1 (client and State) match Sheet 2 table (client and state) return CountofParticipant SSN from Table in sheet2. "Toppers" wrote: =INDEX(F1:F100,MATCH(1,(A1:A100="Custname")*(D1:D1 00="State"),0)) Replace Custname and State by text or Cells containing your values Enter with Ctrl+Shift+Enter OR =SUMPRODUCT(--(A1:A100="Custname"),--(D1:D100="State"),F1:F100) just Enter. "Buzz07" wrote: I'm trying to do a vlookup with 2 sets of criteria. For example: I want to look up a customer name in column A and the customer state in column D, if they both match then bring back the # of employees per state in column F. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup for 2 sets of Criteria (or do I need to use something
This is the formula that I have been trying to get to work. It's obviously
not the answer. Any ideas? =INDEX(StateEmployeeData!C:C,MATCH('ClientStatesOc cupied '!A3&'ClientStatesOccupied '!D2,StateEmployeeData!A:A&StateEmployeeData!B:B,0 ),3) StateEmployeeData = Sheet 2 (data table) ClientStatesOccupied = Sheet 1 (grid I'm wanting the info to go into.) "Toppers" wrote: =INDEX(F1:F100,MATCH(1,(A1:A100="Custname")*(D1:D1 00="State"),0)) Replace Custname and State by text or Cells containing your values Enter with Ctrl+Shift+Enter OR =SUMPRODUCT(--(A1:A100="Custname"),--(D1:D100="State"),F1:F100) just Enter. "Buzz07" wrote: I'm trying to do a vlookup with 2 sets of criteria. For example: I want to look up a customer name in column A and the customer state in column D, if they both match then bring back the # of employees per state in column F. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup for 2 sets of Criteria (or do I need to use something
My Sheet1:
A B C D CA MN SD <=== row 1 John 100 200 500 Bill 300 400 600 My sheet2: A CA < row 1 John 100 <==== formula here in A3 : =INDEX(Sheet1!$B$1:$D$3,MATCH(A1,Sheet1!$B$1:$D$1, 0),MATCH(A2,Sheet1!$A$1:$A$3)) HTH "Buzz07" wrote: This is the formula that I have been trying to get to work. It's obviously not the answer. Any ideas? =INDEX(StateEmployeeData!C:C,MATCH('ClientStatesOc cupied '!A3&'ClientStatesOccupied '!D2,StateEmployeeData!A:A&StateEmployeeData!B:B,0 ),3) StateEmployeeData = Sheet 2 (data table) ClientStatesOccupied = Sheet 1 (grid I'm wanting the info to go into.) "Toppers" wrote: =INDEX(F1:F100,MATCH(1,(A1:A100="Custname")*(D1:D1 00="State"),0)) Replace Custname and State by text or Cells containing your values Enter with Ctrl+Shift+Enter OR =SUMPRODUCT(--(A1:A100="Custname"),--(D1:D100="State"),F1:F100) just Enter. "Buzz07" wrote: I'm trying to do a vlookup with 2 sets of criteria. For example: I want to look up a customer name in column A and the customer state in column D, if they both match then bring back the # of employees per state in column F. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup for 2 sets of Criteria (or do I need to use something
Actual it looks like this:
Sheet 1 (All 50 states run horizontally accross the top, with # of employee's column in between) Column B is a % forumla designed to give me the % of employee's per state once the return value from sheet 2 is in column C. A B C D (CLIENT NAME) State # of Employee's State IBM =IF(E3="", "",E3/$C3) =Index Foluma =IF(E3="", "",E3/$C3) IBM =IF(E3="", "",E3/$C3) =Index Foluma =IF(E3="", "",E3/$C3) IBM =IF(E3="", "",E3/$C3) =Index Foluma =IF(E3="", "",E3/$C3) HP =IF(E3="", "",E3/$C3) =Index Foluma =IF(E3="", "",E3/$C3f) HP =IF(E3="", "",E3/$C3) =Index Foluma =IF(E3="", "",E3/$C3) Sheet 2 (Is the Table of information) A B C (CLIENT NAME) State # of Employees IBM CA 200 IBM AL 100 IBM FL 300 HP CA 4000 HP AL 1500 "Toppers" wrote: My Sheet1: A B C D CA MN SD <=== row 1 John 100 200 500 Bill 300 400 600 My sheet2: A CA < row 1 John 100 <==== formula here in A3 : =INDEX(Sheet1!$B$1:$D$3,MATCH(A1,Sheet1!$B$1:$D$1, 0),MATCH(A2,Sheet1!$A$1:$A$3)) HTH "Buzz07" wrote: This is the formula that I have been trying to get to work. It's obviously not the answer. Any ideas? =INDEX(StateEmployeeData!C:C,MATCH('ClientStatesOc cupied '!A3&'ClientStatesOccupied '!D2,StateEmployeeData!A:A&StateEmployeeData!B:B,0 ),3) StateEmployeeData = Sheet 2 (data table) ClientStatesOccupied = Sheet 1 (grid I'm wanting the info to go into.) "Toppers" wrote: =INDEX(F1:F100,MATCH(1,(A1:A100="Custname")*(D1:D1 00="State"),0)) Replace Custname and State by text or Cells containing your values Enter with Ctrl+Shift+Enter OR =SUMPRODUCT(--(A1:A100="Custname"),--(D1:D100="State"),F1:F100) just Enter. "Buzz07" wrote: I'm trying to do a vlookup with 2 sets of criteria. For example: I want to look up a customer name in column A and the customer state in column D, if they both match then bring back the # of employees per state in column F. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup for 2 sets of Criteria (or do I need to use something
Sorry for the last one... This is a little cleaner.
A B C (CLIENT NAME) State # of Employee's IBM =IF(E3="", "",E3/$C3) =Index Foluma IBM =IF(E3="", "",E3/$C3) =Index Foluma IBM =IF(E3="", "",E3/$C3) =Index Foluma HP =IF(E3="", "",E3/$C3) =Index Foluma HP =IF(E3="", "",E3/$C3) =Index Foluma Sheet 2 (Is the Table of information) A B C (CLIENT NAME) State # of Employees IBM CA 200 IBM AL 100 IBM FL 300 HP CA 4000 HP AL 1500 "Buzz07" wrote: Actual it looks like this: Sheet 1 (All 50 states run horizontally accross the top, with # of employee's column in between) Column B is a % forumla designed to give me the % of employee's per state once the return value from sheet 2 is in column C. A B C D (CLIENT NAME) State # of Employee's State IBM =IF(E3="", "",E3/$C3) =Index Foluma =IF(E3="", "",E3/$C3) IBM =IF(E3="", "",E3/$C3) =Index Foluma =IF(E3="", "",E3/$C3) IBM =IF(E3="", "",E3/$C3) =Index Foluma =IF(E3="", "",E3/$C3) HP =IF(E3="", "",E3/$C3) =Index Foluma =IF(E3="", "",E3/$C3f) HP =IF(E3="", "",E3/$C3) =Index Foluma =IF(E3="", "",E3/$C3) Sheet 2 (Is the Table of information) A B C (CLIENT NAME) State # of Employees IBM CA 200 IBM AL 100 IBM FL 300 HP CA 4000 HP AL 1500 "Toppers" wrote: My Sheet1: A B C D CA MN SD <=== row 1 John 100 200 500 Bill 300 400 600 My sheet2: A CA < row 1 John 100 <==== formula here in A3 : =INDEX(Sheet1!$B$1:$D$3,MATCH(A1,Sheet1!$B$1:$D$1, 0),MATCH(A2,Sheet1!$A$1:$A$3)) HTH "Buzz07" wrote: This is the formula that I have been trying to get to work. It's obviously not the answer. Any ideas? =INDEX(StateEmployeeData!C:C,MATCH('ClientStatesOc cupied '!A3&'ClientStatesOccupied '!D2,StateEmployeeData!A:A&StateEmployeeData!B:B,0 ),3) StateEmployeeData = Sheet 2 (data table) ClientStatesOccupied = Sheet 1 (grid I'm wanting the info to go into.) "Toppers" wrote: =INDEX(F1:F100,MATCH(1,(A1:A100="Custname")*(D1:D1 00="State"),0)) Replace Custname and State by text or Cells containing your values Enter with Ctrl+Shift+Enter OR =SUMPRODUCT(--(A1:A100="Custname"),--(D1:D100="State"),F1:F100) just Enter. "Buzz07" wrote: I'm trying to do a vlookup with 2 sets of criteria. For example: I want to look up a customer name in column A and the customer state in column D, if they both match then bring back the # of employees per state in column F. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlookup with 2 sets of criteria | Excel Discussion (Misc queries) | |||
| SUMIF with two sets of criteria | Excel Discussion (Misc queries) | |||
| for SUMIF function, how do I use 2 sets of range & criteria | Excel Worksheet Functions | |||
| Count rows that match 3 sets of criteria? | Excel Worksheet Functions | |||
| Using Vlookup to look at different names sets of data.... | Excel Worksheet Functions | |||
Hybrid Mode
