VLOOKUP several conditions

I have two worksheets, one with customer data and financial data, and a
second with salesperson information. I need to "import" the
salesperson from the second worksheet into the first worksheet.

Worksheet 1:
Col A Customer Last Name
Col B First Name
Col C Date
Col D Part Number
Remaining columns have financial info


Worksheet 2:
Col A Customer Last Name
Col B First Name
Col C Date
Col D Part Number
Col E Salesperson


I want to have the appropriate salesperson for each sale shown in
Worksheet 1.

There are a few minor problems.
1) Customer last names are sometimes repeated and are not unique.
2) Date of sale is not unique
3) There is no consistency between the two sheets for the column of
customer first name. In one sheet you may have a customer middle
initial or middle name in the column for "First Name"

I though that if I can nest a VLOOKUP it matches a column A, C, and D
from Worksheet 1 to the salesman in Worksheet 2, and places that
salesperson in a new column it may work, but have been unsuccessful.

Any thoughts?


---
Message posted from http://www.ExcelForum.com/

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX('sheet2'!$E$1:$E$100,MATCH(1,('sheet2'!$A$1 :$A$100=A1)*('sheet2'
!$C$1:$C$100=C1)*('sheet2'!$D$1:$D$100=D1),0))




--
Regards
Frank Kabel
Frankfurt, Germany


I have two worksheets, one with customer data and financial data, and
a second with salesperson information. I need to "import" the
salesperson from the second worksheet into the first worksheet.

Worksheet 1:
Col A Customer Last Name
Col B First Name
Col C Date
Col D Part Number
Remaining columns have financial info


Worksheet 2:
Col A Customer Last Name
Col B First Name
Col C Date
Col D Part Number
Col E Salesperson


I want to have the appropriate salesperson for each sale shown in
Worksheet 1.

There are a few minor problems.
1) Customer last names are sometimes repeated and are not unique.
2) Date of sale is not unique
3) There is no consistency between the two sheets for the column of
customer first name. In one sheet you may have a customer middle
initial or middle name in the column for "First Name"

I though that if I can nest a VLOOKUP it matches a column A, C, and D
from Worksheet 1 to the salesman in Worksheet 2, and places that
salesperson in a new column it may work, but have been unsuccessful.

Any thoughts?


---
Message posted from http://www.ExcelForum.com/

Frank,
I neglected to tell you I have column headings.
When I pasted your function into E2 in my Worksheet 1, I got the text
value for the header of the column E.

I changed your forumula to start at row 2

=INDEX('sheet2'!$E$2:$E$100,MATCH(1,('sheet2'!$A$2 :$A$100=A2)*('sheet2'!$C$2:$C$100=C2)*('sheet2'!$D $2:$D$100=D2),0))

but got ##N/A

Help!




Frank Kabel wrote:
*Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX('sheet2'!$E$1:$E$100,MATCH(1,('sheet2'!$A$1 :$A$100=A1)*('sheet2'
!$C$1:$C$100=C1)*('sheet2'!$D$1:$D$100=D1),0))




--
Regards
Frank Kabel
Frankfurt, Germany


I have two worksheets, one with customer data and financial data,
and
a second with salesperson information. I need to "import" the
salesperson from the second worksheet into the first worksheet.

Worksheet 1:
Col A Customer Last Name
Col B First Name
Col C Date
Col D Part Number
Remaining columns have financial info


Worksheet 2:
Col A Customer Last Name
Col B First Name
Col C Date
Col D Part Number
Col E Salesperson


I want to have the appropriate salesperson for each sale shown in
Worksheet 1.

There are a few minor problems.
1) Customer last names are sometimes repeated and are not unique.
2) Date of sale is not unique
3) There is no consistency between the two sheets for the column
of
customer first name. In one sheet you may have a customer middle
initial or middle name in the column for "First Name"

I though that if I can nest a VLOOKUP it matches a column A, C, and
D
from Worksheet 1 to the salesman in Worksheet 2, and places that
salesperson in a new column it may work, but have been
unsuccessful.

Any thoughts?


---
Message posted from http://www.ExcelForum.com/ *


---
Message posted from http://www.ExcelForum.com/

HOLD ON FRANK!

I think it works.
I have some data on worksheet 1 that is not on worksheet 2; I think
that was the problem.


---
Message posted from http://www.ExcelForum.com/

HOLD ON FRANK!

I think it works.
I have some data on worksheet 1 that is not on worksheet 2; I think
that was the problem.


---
Message posted from http://www.ExcelForum.com/

Hi
yes, in this case you get a #NA error :-)

--
Regards
Frank Kabel
Frankfurt, Germany


HOLD ON FRANK!

I think it works.
I have some data on worksheet 1 that is not on worksheet 2; I think
that was the problem.


---
Message posted from http://www.ExcelForum.com/

See also:

http://tinyurl.com/2yvn2

"Ron H " wrote in message
...
I have two worksheets, one with customer data and financial data, and a
second with salesperson information. I need to "import" the
salesperson from the second worksheet into the first worksheet.

Worksheet 1:
Col A Customer Last Name
Col B First Name
Col C Date
Col D Part Number
Remaining columns have financial info


Worksheet 2:
Col A Customer Last Name
Col B First Name
Col C Date
Col D Part Number
Col E Salesperson


I want to have the appropriate salesperson for each sale shown in
Worksheet 1.

There are a few minor problems.
1) Customer last names are sometimes repeated and are not unique.
2) Date of sale is not unique
3) There is no consistency between the two sheets for the column of
customer first name. In one sheet you may have a customer middle
initial or middle name in the column for "First Name"

I though that if I can nest a VLOOKUP it matches a column A, C, and D
from Worksheet 1 to the salesman in Worksheet 2, and places that
salesperson in a new column it may work, but have been unsuccessful.

Any thoughts?


---
Message posted from http://www.ExcelForum.com/