VLOOKUP several conditions

[Ron H]

I have two worksheets, one with customer data and financial data, and
second with salesperson information. I need to "import" th
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 i
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 o
customer first name. In one sheet you may have a customer middl
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
from Worksheet 1 to the salesman in Worksheet 2, and places tha
salesperson in a new column it may work, but have been unsuccessful.

Any thoughts

 

[Frank Kabel]

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))

 

[Ron H]

Frank,
I neglected to tell you I have column headings.
When I pasted your function into E2 in my Worksheet 1, I got the tex
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!

 

[Ron H]

HOLD ON FRANK!

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

 

[Ron H]

HOLD ON FRANK!

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

 

[Frank Kabel]

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

 

[Aladin Akyurek]

See also:

http://tinyurl.com/2yvn2