Rank based on client

[kris01]

Hello, I have a need for a formula. I know how to rank balances in terms of ascending or descending order.

but I need to rank the top 5 values for each each client and bring back multiple sets of data as well.

For example. I need to my formula to bring back the top 5 balances for the client in E3 (Client Apple) and the values in Columns A, D, E on the report tab.

Your urgent help would be greatly appreciated. Please see my excel file.

Example1.xlsx
	A	B	C	D	E
1			Client	Apple
2
3
4
5	Name	Balance	Unit	Status
6	Brian	500	Disney land	current
7	Jane	200	white house	current
8	Ming	150	berry house	current
9	Sarah	100	apple house	current
10	Ian	50	Cherry house	current
11
12
13
report

Example1.xlsx
	A	B	C	D	E
1	client	balance	name	unit	status
2	apple	500	Brian	Disney land	Current
3	apple	200	Jane	white house	Current
4	apple	150	Ming	berry house	Current
5	apple	100	Sarah	apple house	Current
6	apple	50	Ian	Cherry house	Current
7	apple	5	mike	banana house	Current
8	apple	14	Rob	strawberry house	Current
9	apple	2	kris	Ike house	Current
10	apple	1	Chris	plus house	Current
11	banana	500	Brian	plus house	Current
12	banana	200	Jane	plus house	Current
13	banana	150	Ming	plus house	Current
14	banana	100	Sarah	plus house	Current
15	banana	50	Ian	plus house	Current
16	banana	5	mike	plus house	Current
17	banana	14	Rob	plus house	Current
18	banana	2	kris	plus house	Current
19	banana	1	Chris	plus house	Current
Raw

​

 

[kris01]

sorry I mean D1 not E3