Top ten list using lookup functions - offset?

[michellebno]

Hi, I am trying to list the top 10 accounts for my sales people. There may be more or less than 10 accounts for each sale person. I was going to use vlookup for the first one and then offset, but vlookup is a range and doesn't work that way. I just learned about IndexMatch, but it's finding the last instance of the sales person's name, not the first. I've attached an example file. I'm wanting find Bob Smith, return Kroger and it's sales, then use OFFSET to show me the next 4 underneath (or any other solution to build this). But instead of giving me Kroger, it's giving me the last one for Bob Smith, Chevron. Suggestions?

 

[Toadstool]

Hi Michellebno,

This should work for you

Michellebno.xlsx
	A	B	C	D	E	F	G
1	Bob Smith				Salesperson	Customer	Sales
2	Rank	Top Customers	Cases Sold		Bob Smith	Kroger	164
3	1	Kroger	164		Bob Smith	Costco	77
4	2	Costco	77		Bob Smith	Cust3	66
5	3	Cust3	66		Bob Smith	Cust4	55
6	4	Cust4	55		Bob Smith	Cust5	55
7	5	Cust5	55		Bob Smith	Cust6	33
8					Bob Smith	Cust7	22
9	Lucy Brown				Bob Smith	Cust8	11
10	Rank	Top Customers	Cases Sold		Susan Thomas	ACME	200
11	1	Costco	220		Susan Thomas	Bodgit	22
12	2	Cust1	66		Lucy Brown	Costco	220
13	3				Lucy Brown	Cust1	66
14	4
15	5
16
Sheet1

Cell Formulas
Range	Formula
B11:C15,B3:C7	B3	=IFERROR(INDEX(F$2:F$9999,AGGREGATE(15,6,ROW($F$2:$F$9999)-ROW($F$1)/(($E$2:$E$9999=INDEX($A$1:$A$9999,ROW()-$A3-1))),$A3)),"")

 

[Toadstool]

...and here's another version using OFFSET

Michellebno.xlsx
	A	B	C	D	E	F	G
1	Bob Smith				Salesperson	Customer	Sales
2	Rank	Top Customers	Cases Sold		Bob Smith	Kroger	164
3	1	Kroger	164		Bob Smith	Costco	77
4	2	Costco	77		Bob Smith	Cust3	66
5	3	Cust3	66		Bob Smith	Cust4	55
6	4	Cust4	55		Bob Smith	Cust5	55
7	5	Cust5	55		Bob Smith	Cust6	33
8					Bob Smith	Cust7	22
9	Lucy Brown				Bob Smith	Cust8	11
10	Rank	Top Customers	Cases Sold		Susan Thomas	ACME	200
11	1	Costco	220		Susan Thomas	Bodgit	22
12	2	Cust1	66		Lucy Brown	Costco	220
13	3				Lucy Brown	Cust1	66
14	4
15	5
Sheet1 (2)

Cell Formulas
Range	Formula
B11:C15,B3:C7	B3	=IF(COUNTIF($E$2:$E$9999,INDEX($A$1:$A$9999,ROW()-$A3-1))>=$A3,OFFSET(F$1,MATCH(INDEX($A$1:$A$9999,ROW()-$A3-1),$E$2:$E$9999,0)-1+$A3,0),"")

 

[Fluff]

Hi & welcome to MrExcel.
Another option

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	Bob Smith				Salesperson	Customer	Sales
2	Rank	Top Customers	Cases Sold		Bob Smith	Kroger	164
3	1	Kroger	164		Bob Smith	Costco	77
4	2	Costco	77		Bob Smith	Cust3	66
5	3	Cust3	66		Bob Smith	Cust4	55
6	4	Cust4	55		Bob Smith	Cust5	55
7	5	Cust5	55		Bob Smith	Cust6	33
8					Bob Smith	Cust7	22
9	Lucy Brown				Bob Smith	Cust8	11
10	Rank	Top Customers	Cases Sold		Susan Thomas	ACME	200
11	1	Costco	220		Susan Thomas	Bodgit	22
12	2	Cust1	66		Lucy Brown	Costco	220
13	3				Lucy Brown	Cust1	66
14	4
15	5
16
Lists

Cell Formulas
Range	Formula
B3:C7	B3	=LET(Fltr,FILTER(F2:G20,E2:E20=A1),INDEX(Fltr,SEQUENCE(MIN(ROWS(Fltr),5)),{1,2}))
B11:C12	B11	=LET(Fltr,FILTER(F2:G20,E2:E20=A9),INDEX(Fltr,SEQUENCE(MIN(ROWS(Fltr),5)),{1,2}))
Dynamic array formulas.

 

[Peter_SSs]

.. another possible variation to consider.

21 05 03.xlsm
	A	B	C	D	E	F	G
1	Bob Smith				Salesperson	Customer	Sales
2	Rank	Top Customers	Cases Sold		Bob Smith	Kroger	164
3	1	Kroger	164		Bob Smith	Costco	77
4	2	Costco	77		Bob Smith	Cust3	66
5	3	Cust3	66		Bob Smith	Cust4	55
6	4	Cust4	55		Bob Smith	Cust5	55
7	5	Cust5	55		Bob Smith	Cust6	33
8					Bob Smith	Cust7	22
9	Lucy Brown				Bob Smith	Cust8	11
10	Rank	Top Customers	Cases Sold		Susan Thomas	ACME	200
11	1	Costco	220		Susan Thomas	Bodgit	22
12	2	Cust1	66		Lucy Brown	Costco	220
13					Lucy Brown	Cust1	66
14
Top N

Cell Formulas
Range	Formula
A3:A7,A11:A12	A3	=SEQUENCE(MIN(5,COUNTIF(E$2:E$13,A1)))
B3:C7	B3	=INDEX(FILTER(F2:G13,E2:E13=A1),A3#,{1,2})
B11:C12	B11	=INDEX(FILTER(F2:G13,E2:E13=A9),A11#,{1,2})
Dynamic array formulas.

 

[michellebno]

There are great, thank you very, very much!

 

[Fluff]

Glad we could help & thanks for the feedback.