Function required - Top 10 with extra columns & duplicates

[adamsherring]

(this is a repost from another forum, I asked for the original post to be deleted)

Hi,

While i'm comfortable with excel, I don't know some of the common workarounds, and while I've found some solutions that may fit here, there wasn't any accompanying description so I thought i'd post something new.

What I need is to have a function which will match something from one column and return a value from another.

I have been using index & match to do this - but it does not handle duplicates. I will have many, legitimate duplicates, and I require them to show up as well.

My basic need is this. I have 3 columns to check and order. Each ordered list (1 for each column) will also have to reference the correct value from the 1st column.

So lets say steve and bob both have a value of '4'. In the list that compiles that column, I would need both steve and bob to be represented. My current setup of index & match will show the two '4' values (I use large()), but will only match the first name it finds.

If the formula is long I would appreciate if you explained it so I will be able to mold it to my needs.

My current function is : =INDEX(A:A,MATCH(LARGE(B:B,ROW()-1),B:B))

thanks for your help

[martindwilson]

this sheet should show you how to construct an array formula to do what you want. this one looks at a2 and pulls corresponding data from table f2:h13
formulas go in b2 and c2 (confirmed with ctrl+alt+enter) then dragged down

[adamsherring]

Hi again,

Thank you for your help, this appears to be what I am looking for.

However, can you help me a bit more by explaining the portions of this function?

Please Login or Register  to view this content.

Also, in your example, in the first column, I would be using this function to obtain a list of dates (in my case, integers) : =large(F:F,row()-1)

So each row would have its own value, and the function would not look to the same absolute cell, but the one to its immediate left.


EDIT : I have just poked around your function, and it seems to determine the number of matches with the ROW()-1 argument. Unfortunately, this will not work for me, as there will be different, non-duplicate values in the same columns as the duplicate ones.

I've attached an example which shows what I'm after and my current problem.

Thanks again for your help,

Adam