Return Column of Reference for LARGE Array

[sp0ck1]

...or any other formula.

This seems like Excel 101 but I can't nail down what I'm missing.

I have a header column full of names in a matrix of sales figures for each day. All I want to do is, on a separate sheet in the same workbook, identify the top 5 best days in whatever column that name is in. I gave up back when I made this and just manually looked up which array corresponded to each person, but I'm going to need to know for future projects. And besides, this seems like Excel 101!

I could have just named the ranges, but it might not always be that simple. How do I direct a MATCH or whatever formula to return a certain array where the header matches a specific criteria so I can just change the criteria in the cell the formula is directed to instead of manually updating to add in each range?

Example is attached. Thank you!

[mhovidz]

Hi sp0ck1,

I already had solution in your problem. What I did is to use the following formula

• Please Login or Register  to view this content.

• Please Login or Register  to view this content.

• Please Login or Register  to view this content.

• Please Login or Register  to view this content.

Please see attached file for your reference

[sp0ck1]

Thank you so much! Understanding how this works will be helpful in many different applications.

-Cory

[Glenn Kennedy]

I don't recommend using OFFSET, unless you have to. It recalculates everytime anything changes. Try this insted. To return amounts:

=IFERROR(LARGE(INDEX(Matrix!$D$2:$AE$500,,MATCH($B$1,Matrix!$D$1:$AE$1,0)),ROWS($1:1)),"")

to return dates:

=IFERROR(INDEX(Matrix!$B$2:$B$500,MATCH(Data!B3,INDEX(Matrix!$D$2:$AE$500,,MATCH($B$1,Matrix!$D$1:$AE$1,0)),0)),"")

see sheet.