Insert a value in a cell based upon a comparison of cell values in 2 separate worksheets

[Doctorjones_md]

What would be the best way to approach this?

I have a hugh list of transactions that I need to sort on -- each
transaction has a Salesperson with falls into (2) distinct Sales Categories:
(Private) & (Commercial).

For reasons beyond my control, the decision was made not to include these
category fields on the worksheets, so I created a listing of all the
Salesperson with their corresponding Sales Categories in a separate
worksheet.


I have the following code -- which does a VLOOKUP on Cell F2
(Salesperson1!F2) against Salesperson2!A2:A4000)
CODE:

=VLOOKUP(F2,Salesperson2!A2:A4000,1,FALSE)

Here's what I'm trying to do ...

I'd like use the values in Salesperson!F2:F4000 and search for these valuse
in Salesperson!A2:A4000 -- for each corresponding MATCH (ie,
Salesperson1!F2 = Salesperson2!A2, insert the value of Salesperson2!B2 into
Salesperson1!K2).

What would be the best way to approach this? I suspect that I'll need to
Loop through records Salesperson1!F2:F4000.

Thanks in advance

 

[Dave Peterson]

I may be missing something, but it looks like you want to match up the value in
F2 to something in column A of the worksheet name salesperson2 and return the
value from column F.

If that's the case, then this would go in K2:
=vlookup(f2,SalesPerson2!$a$2:$F$4000,6,false)

Debra Dalgleish has lots of notes on =vlookup():
http://www.contextures.com/xlFunctions02.html

 

[Doctorjones_md]

Dave,

Thank you for the quick reply -- I want to return the value from column B of
Salesperson2, so I had the following formula in Cell K2 of Salesperson1:
=VLOOKUP(F2,Salesrep1!A2:B1500,2,FALSE)

This would work great if the data in each worksheet was evenly matched.
Here's my problem ...

In Salesperson1, I have 1500 entries (a total of 102 sales reps spread over
1500 transactions). The first 20 rows (A2:A21) in Salesperson1 are for
sales rep Scott Anderson, but the only reference to Scott Anderson is in
cell A3 of Salesperson2. I need for the code to take the value of
Salesperson1!Ax (where x is the row) and find that value in Salesperson2!Ax
(where x is the row) and return the value of Salesperson!Bx (where x is the
row) in Salesperson1!Kx (where x is the row).

Example of data:
Worksheet SALESPERSON1
Worksheet SALESPERSON2

Salesperson (Cell Ax) Department (cell Kx)
Salesperson (Cell Ax) Department (Cell Bx)
Scott Anderson
Greg Albert 10
Scott Anderson
Scott Anderson 5
Scott Anderson
Tina Alyson 2
Scott Anderson
Debbie Baker 1
Scott Anderson
Tim Davis 7
Scott Anderson
Scott Anderson
Scott Anderson
Tim Davis


Any ideas on how to achieve this?

Thanks again for your help.
, and the

 

[Dave Peterson]

I would spend some time putting a key name on each of the rows in that second
worksheet.

 

[Doctorjones_md]

Dave,

Thanks for your recommendation -- I was looking into your suggestion, then
found that my code -- (=VLOOKUP(F2,Salesperson!$A$2:$B$1500,2,TRUE) --
entered in Cell K2 with a slight modification of the code -- gave me exactly
what I was looking for -- I just needed to change to Absolute Reference and
change FALSE to TRUE

 

[Dave Peterson]

Since you're matching on a name, I bet you want an exact match.

I'd use:
=VLOOKUP(F2,Salesperson!$A$2:$B$1500,2,false)

Dave,

Thanks for your recommendation -- I was looking into your suggestion, then
found that my code -- (=VLOOKUP(F2,Salesperson!$A$2:$B$1500,2,TRUE) --
entered in Cell K2 with a slight modification of the code -- gave me exactly
what I was looking for -- I just needed to change to Absolute Reference and
change FALSE to TRUE

 

[Doctorjones_md]

Dave,

Thanks for catching that -- I didn't have any mismatches on names, but I
changed Gomez to Gomes in Salesperson1 and saw the N/A value return when I
made the modification your suggested.
Thanks for all your time and patience -- this is a very valuable forum!

 

[Dave Peterson]

Glad you found out about the difference before it was too late!

Dave,

Thanks for catching that -- I didn't have any mismatches on names, but I
changed Gomez to Gomes in Salesperson1 and saw the N/A value return when I
made the modification your suggested.
Thanks for all your time and patience -- this is a very valuable forum!