VLOOKUP several conditions

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

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

0
6/12/2004 4:39:49 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
618 Views

Similar Articles

[PageSpeed] 19

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/

0
frank.kabel (11126)
6/12/2004 4:51:53 AM
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!




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 colum
> 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, an
> D
> > from Worksheet 1 to the salesman in Worksheet 2, and places that
> > salesperson in a new column it may work, but have bee
> unsuccessful.
> >
> > Any thoughts?
> >
> >
> > ---
> > Message posted from http://www.ExcelForum.com/ 

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

0
6/12/2004 5:32:22 AM
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

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

0
6/12/2004 5:37:18 AM
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

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

0
6/12/2004 5:44:13 AM
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/
0
frank.kabel (11126)
6/12/2004 7:22:50 AM
See also:

http://tinyurl.com/2yvn2

"Ron H >" <<Ron.H.17q0ob@excelforum-nospam.com> wrote in message
news:Ron.H.17q0ob@excelforum-nospam.com...
> 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/
>


0
akyurek (248)
6/13/2004 7:33:22 AM
Reply:

Similar Artilces: