Top 5 based on selection from drop down - index/match??

[thedudelord]

Hi,

I have my workbook set up to show a separate worksheet for each city. The representative from each city will go into their own city worksheet and select their city from a drop down list. When the city is selected, I need to bring in top 5 past due invoices for that city - data set is on a hidden worksheet. Any ideas on how to do this with a formula? I've scoured the internet and cannot find anything. Thank you!

City worksheet:
Drop Down = Atlanta

Need to bring in top 5 instances (highest to lowest Past Due amount) to show:
Cell C53:C57 = Customer #
Cell D53:D57 = Customer Name
F53:F57 = # of past due invoices
G53:G57 = Past Due Amount

Data Worksheet:
Column C = City Name
Column D = Customer #
Column E = Customer Name
Column L = # of Invoices
Column M = Past Due Amount

 

[AhoyNC]

Try:
The formula in C53 is an array formula and must be entered with CTRL-SHIFT-ENTER. Then drag down.
The other formulas just use ENTER and drag or copy down.

Excel Workbook
	A	B	C	D	E	F	G
48
49			City
50			Atlanta
51
52			Customer #	Customer Name		# of Invoices	Past Due $
53			Cust16	Name16		2	1589
54			Cust12	Name12		5	1483
55			Cust5	Name5		4	1385
56			Cust10	Name10		3	1348
57			Cust4	Name4		4	1118
City

Excel Workbook
	C	D	E	F	K	L	M
1	City	Customer #	Customer Name			# of Invoices	Past Due $
2	Atlanta	Cust1	Name1			4	227
3	Charlotte	Cust2	Name2			2	1144
4	Chicago	Cust3	Name3			5	906
5	Atlanta	Cust4	Name4			4	1118
6	Atlanta	Cust5	Name5			4	1385
7	Charlotte	Cust6	Name6			4	229
8	Chicago	Cust7	Name7			5	1704
9	Atlanta	Cust8	Name8			2	583
10	Charlotte	Cust9	Name9			3	630
11	Atlanta	Cust10	Name10			3	1348
12	Chicago	Cust11	Name11			2	1087
13	Atlanta	Cust12	Name12			5	1483
14	Charlotte	Cust13	Name13			2	1553
15	Atlanta	Cust14	Name14			2	1056
16	Charlotte	Cust15	Name15			2	1658
17	Atlanta	Cust16	Name16			2	1589
18	Charlotte	Cust17	Name17			2	588
19	Chicago	Cust18	Name18			4	55
Data