Return Top N values based of Reference ID

[bambamclint]

Hi,

I am still stumped on this top purchase list with this workbook. I made a lot of changes, moved some stuff and deleted a lot of fluff.

Account_Summary
Master_Territory (Master_Table)
Client_Purchases (cPurch Table)

I have got this formula on the Account_Summary sheet but it is returning the 3rd row not the 1st nor is it returning the right values for different accounts:

=INDEX(cPurch[Line_Name],MATCH($C$8,Master_Table[Customer Number],0),0) <- i understand that is not the correct - i was just happy i had something returned.

I would like to return all the lines in a list based on the account id on the Account Summary Sheet

I do not know if this is a SUMIF or Large or even the syntax for the formula.

[sergiomabres]

Hi Bamba
I think you could use the strategy "Get nth match with INDEX / MATCH"

After a while in your sheet I got (see atached image)

Please Login or Register  to view this content.

Using the formulas

Please Login or Register  to view this content.

B21, G21, and J21 are matrix formula (Control+Ship+Enter)

Then you copy down to row 50

I had to break all the cell combination because they dont work with matrix formulas

You may like to copy this to you browser to see your sheet with the new formulas 1drv.ms/x/s!AovCE1fDrrdSmmHM9LlFrmcdXoPw

You can