# Names on two different sheets, trying to look up value

#### fras3435

##### New Member
I have two sheets with names on them. One has just names (names of sales people who just made a sale) and the other has the names of sales people with the amount of sales they had over the past 12 months. I pull data regularly to see who the most active sales people are. I want to be able to figure out how many sales the new people have had and then be able to sort that list by most sales to fewest sales.

Here's a link to a video I shot about it for some clearer instructions (it's only 40 second long): Loom | Send a video. Not a thousand words.

Here's a link to a sample sheet: Sales Person Example.xlsx

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### Trebor76

##### Well-known Member
Hi fras3435,

Welcome to MrExcel!!

Put this formula into cell B2 of Sheet2...

=IFERROR(VLOOKUP(A2,Sheet1!A:B,2,FALSE),A2 & " is not on the sales tab")

...and copy it down as many rows as required (B15 in your posted example). You would then sort the dataset via the Sort & Filter tab from the Data ribbon.

I would suggest to avoid just using first names for the lookup because if there are two or more staff members with the same first name the VLOOKUP function always returns the first match it finds. Perhaps you could use full names or employee codes?

Regards,

Robert

#### Fluff

##### MrExcel MVP, Moderator
Another option
Sales Person Example.xlsx
ABCD
1Name
2LoriLori36
3JonnyAirika25
4JanisLisa17
5DarrelShawn13
6RafeJanis10
7Stevenmoana9
8moanaRafe3
9ShawnDarrel2
10JoelJonny0
11AirikaSteven0
12LisaJoel0
13AlexAlex0
14DonDon0
15SeanSean0
Sheet2
Cell Formulas
RangeFormula
C2:D15C2=SORT(CHOOSE({1,2},A2:A15,SUMIFS(Sheet1!B:B,Sheet1!A:A,A2:A15)),2,-1)
Dynamic array formulas.

Replies
1
Views
197
Replies
1
Views
125
Replies
2
Views
302
Replies
8
Views
110
Replies
0
Views
146

1,181,639
Messages
5,931,158
Members
436,779
Latest member
linlaz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back