Names on two different sheets, trying to look up value

[fras3435]

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

 

[Trebor76]

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]

Another option

Sales Person Example.xlsx
	A	B	C	D
1	Name
2	Lori		Lori	36
3	Jonny		Airika	25
4	Janis		Lisa	17
5	Darrel		Shawn	13
6	Rafe		Janis	10
7	Steven		moana	9
8	moana		Rafe	3
9	Shawn		Darrel	2
10	Joel		Jonny	0
11	Airika		Steven	0
12	Lisa		Joel	0
13	Alex		Alex	0
14	Don		Don	0
15	Sean		Sean	0
Sheet2

Cell Formulas
Range	Formula
C2:D15	C2	=SORT(CHOOSE({1,2},A2:A15,SUMIFS(Sheet1!B:B,Sheet1!A:A,A2:A15)),2,-1)
Dynamic array formulas.