| 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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup for 2 sets of Criteria (or do I need to use something
My original formula was what you requi
=INDEX(Sheet2!C1:C100,MATCH(1,(Sheet2!A1:A100=A2)* (Sheet2!B1:B100=B2),0)) Enter with Ctrl+Shift+enter OR =INDEX(Sheet2!$A$1:$C$100,MATCH(A2,Sheet2!$A$1:$A$ 100,0),MATCH(B2,Sheet2!$B$1:$B$100)) with Enter "Buzz07" wrote: 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. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup for 2 sets of Criteria (or do I need to use something
This formula appears to be pulling against a list for both conditions...It's
not working. Check out my example below. It may help to put what I have written into word or excel so you can see it all at one time. Sorry... but I am struggling with this formula:) Sheet 1 A B C D E Client Ctrl# Population AL # of Empl Advantage 10871 12000 (if F) (unknow F) AmeriCredit 10872 3700 (if F) (unknow F) Andrew Corp 39514 2666 (if F) (unknow F) Asurion 11087 4200 (if F) (unknow F) A T Kearney Inc 19083 659 (if F) (unknow F) Sheet 2 (Data table dumped from Access) A B C State Client CountOfParticipant SSN AL HP 100 AR HP 200 CA HP 700 AL Asurion 300 AR Asurion 10 CA Kmart 102 TX Walmart 109 I am looking for something that says, where Sheet 1 A3 and D2 match with A:A, B:B from Sheet 2, return value column C Repeat in next line A4 and D2 and so on through the grid "Toppers" wrote: My original formula was what you requi =INDEX(Sheet2!C1:C100,MATCH(1,(Sheet2!A1:A100=A2)* (Sheet2!B1:B100=B2),0)) Enter with Ctrl+Shift+enter OR =INDEX(Sheet2!$A$1:$C$100,MATCH(A2,Sheet2!$A$1:$A$ 100,0),MATCH(B2,Sheet2!$B$1:$B$100)) with Enter "Buzz07" wrote: 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. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup for 2 sets of Criteria (or do I need to use something
I got it man...Thanks for help!!!!!!!!!
"Buzz07" wrote: This formula appears to be pulling against a list for both conditions...It's not working. Check out my example below. It may help to put what I have written into word or excel so you can see it all at one time. Sorry... but I am struggling with this formula:) Sheet 1 A B C D E Client Ctrl# Population AL # of Empl Advantage 10871 12000 (if F) (unknow F) AmeriCredit 10872 3700 (if F) (unknow F) Andrew Corp 39514 2666 (if F) (unknow F) Asurion 11087 4200 (if F) (unknow F) A T Kearney Inc 19083 659 (if F) (unknow F) Sheet 2 (Data table dumped from Access) A B C State Client CountOfParticipant SSN AL HP 100 AR HP 200 CA HP 700 AL Asurion 300 AR Asurion 10 CA Kmart 102 TX Walmart 109 I am looking for something that says, where Sheet 1 A3 and D2 match with A:A, B:B from Sheet 2, return value column C Repeat in next line A4 and D2 and so on through the grid "Toppers" wrote: My original formula was what you requi =INDEX(Sheet2!C1:C100,MATCH(1,(Sheet2!A1:A100=A2)* (Sheet2!B1:B100=B2),0)) Enter with Ctrl+Shift+enter OR =INDEX(Sheet2!$A$1:$C$100,MATCH(A2,Sheet2!$A$1:$A$ 100,0),MATCH(B2,Sheet2!$B$1:$B$100)) with Enter "Buzz07" wrote: 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 | |||
Linear Mode
