Lookup values using multiple criteria and based on order

Question

I have a table of data (in this case property rents) and want to return on a seperate sheet only the top three tenants based on the rent. All property data is saved in the same sheet, and each property has a unique reference which can be used as lookup value.

Input

Property    Property name Tenant code Tenant    Rent
bb107       Property 1    t0000610    Tenant 1  115,000.00
bb107       Property 1    t0000515    Tenant 2  120,000.00
bb107       Property 1    t0000630    Tenant 3  125,000.00
bb107       Property 1    t0000565    Tenant 4  130,000.00
bb107       Property 1    t0000579    Tenant 5  145,000.00
bb110       Property 2    t0000520    Tenant 6  220,000.00
bb110       Property 2    t0000604    Tenant 7  215,000.00
bb110       Property 2    t0000588    Tenant 8  230,000.00
bb110       Property 2    t0000586    Tenant 9  185,000.00
bb110       Property 2    t0000611    Tenant 10 250,000.00
bb110       Property 2    t0000568    Tenant 11 235,000.00
bb115       Property 3    t0000562    Tenant 12 85,000.00
bb115       Property 3    t0000631    Tenant 13 65,000.00
bb115       Property 3    t0000532    Tenant 14 70,000.00
bb115       Property 3    t0000589    Tenant 15 72,500.00
bb115       Property 3    t0000556    Tenant 16 78,750.00
bb115       Property 3    t0000613    Tenant 17 55,000.00
bb115       Property 3    t0000524    Tenant 18 67,500.00

Could someone please tell me the argument I should use on my sheet to a) reference the cells in my lookup sheet relating only to my specicfic property. I have mnay properties (100+) and cannot define a name for each. b) how to return the tenant references for the top 3 entries. I can always lookup the other values once I know the top 3 tenants.

Output should be something like this:

Property 1

bb107   Property 1  t0000579    Tenant 5    145,000.00
bb107   Property 1  t0000565    Tenant 4    130,000.00
bb107   Property 1  t0000630    Tenant 3    125,000.00

Property 2

bb110   Property 2  t0000611    Tenant 10   250,000.00
bb110   Property 2  t0000568    Tenant 11   235,000.00
bb110   Property 2  t0000588    Tenant 8    230,000.00

Property 3

bb115   Property 3  t0000562    Tenant 12   85,000.00
bb115   Property 3  t0000556    Tenant 16   78,750.00
bb115   Property 3  t0000589    Tenant 15   72,500.00

Another question would be, if I gave some of the entries a specific reference, how could I pull only those entries through onto my output page. I am struggling with giving excel multiple criteria to use for the output (ie. look for property 1, then look for highest value rent, then look for second highest (or manually entered code) etc....

Thanks!

MikeD MikeD · Accepted Answer · 2012-08-23T16:57:21

You can use a Pivot table in a second sheet with fields Property, Property name, Tenant code, Tenant at the Row level and Sum of Rent in the Data area.
As you have all your key fields at row level ther is no aggregation
on the lowest level (Tenant) you change the advanced field options to "Sort Descending by Sum of Rent" and "Top10 AutoShow On" / Show Top 3 / using field "Sum of Rent"
now you filter for the desired tenant name voi-la ... that should be it

Lookup values using multiple criteria and based on order

1 Answers