ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Vlookup for 2 sets of Criteria (or do I need to use something else (https://www.excelbanter.com/excel-discussion-misc-queries/155457-vlookup-2-sets-criteria-do-i-need-use-something-else.html)

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

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

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

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

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

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

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

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

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

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


All times are GMT +1. The time now is 10:11 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com