Excel 2010 - Large function across multiple columns, which each have their own criteria

[Zilar]

HI Guys,
I've searched the forum for answers, but can't find one that solves my particular problem.
I have attached the spreadsheet.
Firstly, I need to find the Top 5 biggest sales across columns D, F, J, L; each of these columns have qualifying criteria ("AI", as opposed to "A", etc) in their corresponding columns (E, G, K, M). This data (amounts) then get inputted in a different sheet (Top 5...). i've played around with Large(if) as well as Large(or) statements, but no avail.

2ndly, I also need the matching Client Name added. ( I have played around with Index & Match, but they only seem to work if they have to match one column.
There are actually more columns, besides DFJ& L, there are 6 to be precise, but I am hoping this example I can just be adapted for all my regions.
Excel Functions for Top 5 wins.xlsx
Please can someone shed some light?
I hope I explained it correctly.
Many thanks

[alvin-chung]

Hi, try steps below to get the top 5 wins.
1. Create 4 names as follow:
PlantsMaintenance='Region Data_Annuity'!$D$4:$D$76*('Region Data_Annuity'!$E$4:$E$76="AI")
PlantsRental='Region Data_Annuity'!$F$4:$F$76*('Region Data_Annuity'!$G$4:$G$76="AI")
WaterMaintenance='Region Data_Annuity'!$J$4:$J$76*('Region Data_Annuity'!$K$4:$K$76="AI")
WaterRental='Region Data_Annuity'!$L$4:$L$76*('Region Data_Annuity'!$M$4:$M$76="AI")
2. Enter array formula (ctrl+shift+enter) in 'Top 5 Wins'!C3

Please Login or Register  to view this content.

3. Copy the formula down to C7 and you shall get all the top 5 annuity gains

As for your second issue, what if more than 1 client names matched the top 1 win?


_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[Zilar]

Hi Alvin,
Thanks for all the help so far. I am implementing step 1 so far.
I dont think that there will be more than 1 client that matches the Top 1 Win, as the formula needed, needs to look at the specific line items.

[alvin-chung]

In that case, try this formula in D3 and copy down to D7.

Please Login or Register  to view this content.

_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin

[Zilar]

It works like magic. Thanks Alvin.

[alvin-chung]

Thank you for your feedback, you're most welcome



_______________________________________________________________________________________________________________________________
1. Click on the * Add Reputation if you think someone helped you
2. Mark your thread as SOLVED when question is resolved

Alvin