Return top 5 highest values + corresponding data

[BrutalLogiC]

hello experts I am really struggling to write a formula for the cells in orange. I'm trying to populate the cells in orange so the formula looks at my "data table" sheet and returns the top 5 results (in terms of value) plus the corresponding client and service. The results need to tie up with fixed data for bid status and entity as there are lots of different bid statuses and entities.

Right now I'm just manually typing in the data each week... hopefully someone can offer a solution!

Bid database.xlsx
	B	C	D	E
4	TABLE 1
5	Bid status:	Under preparation
6	Entity:	Company A
7
8		Client	Services	Value
9		Paul	detail of services	5,000
10		Moh	detail of services	900
11		Geoff	detail of services	600
12		Neil	detail of services	500
13		Valentina	detail of services	400
14
15	TABLE 2
16	Bid status:	Submitted
17	Entity:	Company B
18
19		Client	Services	Value
20
21
22
23
24
Summary

Bid database.xlsx
	C	D				H	I		K
3	Bid status	Entity				Client	Services		Value
4	Under preparation	Company C				Paul	detail of services		5,000
5	Under preparation	Company C				Moh	detail of services		900
6	Under preparation	Company A				Geoff	detail of services		600
7	Under preparation	Company A				Neil	detail of services		500
8	Under preparation	Company B				Valentina	detail of services		400
9	Under preparation	Company C				Fatima	detail of services		200
10	Under preparation	Company B				David	detail of services		150
11	Submitted	Company A				Paul	detail of services		950
12	Submitted	Company A				Ava	detail of services		620
13	Submitted	Company B				Alan	detail of services		1,500
14	Submitted	Company B				Neil	detail of services		90,000
15	Submitted	Company C				Geoff	detail of services		600
16	Submitted	Company C				Valentina	detail of services		350
17	Submitted	Company C				David	detail of services		380
18	Submitted	Company C				Fatima	detail of services		490
19	Lost	Company A				Paul	detail of services		560
20	Lost	Company A				Moh	detail of services		810
21	Lost	Company B				Paul	detail of services		930
22	Lost	Company B				Ava	detail of services		810
23	Lost	Company C				Alan	detail of services		650
24	Won	Company C				Neil	detail of services		950
25	Won	Company C				Geoff	detail of services		450
26	Won	Company A				Valentina	detail of services		6,000
27	Won	Company A				David	detail of services		890
28	Won	Company B				Fatima	detail of services		500
Data table

 

[jasonb75]

Should the entity / company name be used as criteria? The layout implies that it should but the the example results do not, is this what you're looking for?

Book1
	B	C	D	E
4	TABLE 1
5	Bid status:	Under preparation
6	Entity:	Company A
7
8		Client	Services	Value
9		Geoff	detail of services	600
10		Neil	detail of services	500
11
12
13
14
15	TABLE 2
16	Bid status:	Submitted
17	Entity:	Company B
18
19		Client	Services	Value
20		Neil	detail of services	90000
21		Alan	detail of services	1500
22
23
24
Summary

Cell Formulas
Range	Formula
C9:D13	C9	=IF($E9="","",INDEX('Data Table'!H:H,AGGREGATE(15,6,ROW('Data Table'!H$4:H$28)/('Data Table'!$K$4:$K$28=$E9)/('Data Table'!$C$4:$C$28=$C$5)/('Data Table'!$D$4:$D$28=$C$6),COUNTIF($E$9:$E9,$E9))))
E9:E13	E9	=IFERROR(AGGREGATE(14,6,'Data Table'!$K$4:$K$28/('Data Table'!$C$4:$C$28=$C$5)/('Data Table'!$D$4:$D$28=$C$6),ROWS(E$9:E9)),"")
C20:D24	C20	=IF($E20="","",INDEX('Data Table'!H:H,AGGREGATE(15,6,ROW('Data Table'!H$4:H$28)/('Data Table'!$K$4:$K$28=$E20)/('Data Table'!$C$4:$C$28=$C$16)/('Data Table'!$D$4:$D$28=$C$17),COUNTIF($E$20:$E20,$E20))))
E20:E24	E20	=IFERROR(AGGREGATE(14,6,'Data Table'!$K$4:$K$28/('Data Table'!$C$4:$C$28=$C$16)/('Data Table'!$D$4:$D$28=$C$17),ROWS(E$20:E20)),"")

 

[BrutalLogiC]

Should the entity / company name be used as criteria? The layout implies that it should but the the example results do not, is this what you're looking for?

yes you are right, I did not copy-paste it across right! entity matters

 

[BrutalLogiC]

works great, thanks so much, I spent a lot of time trying to do this and really I was nowhere near

 

[jasonb75]

I spent a lot of time trying to do this and really I was nowhere near

That might not be strictly true, there are a couple of other ways to do the same thing (with and without helper columns), I just find this to be the most flexible and most reliable method, others may have a different preference.

It might be a good idea to format the database as a table or use dynamic named ranges to that the formulas adjust to the data correctly. After setting up the table you will need to correct the formulas to match the table names, once you have done that any newly added rows will be included automatically.