HLookup / Top Ten listing Help

[visitor83]

Hi Everyone,

Question - I have a spreadsheet with say 50 rows and 50 columns, I want to pull through onto a new sheet the top ten listing from the bottom of the 50 rows but the name of the person from the top of the 50 rows...

So far I have the top ten in figures coming through on the following formula going from B27 = 1 to B37 =10:
=SMALL('Summary - Refunds by User'!$F$42:$AJ$42,B27)

And I have the names that I thought I would be able to pull through on a HLookup, but it doesn't work to pull through the name from the top row, because then the table array would have to include all rows from 1 - 50, but I can't have that, because there may be duplication on the numbers in the other 50 rows.

Hope this makes sense and someone can help!

Thanks
Vic

 

[Matty]

Could you post a small sample of your data set up as well as illustrating what you want the formula to do?

Matty

 

[visitor83]

I can't seem to be able to post any data / picture but here is an example..

Week Staff 1 Staff 2 Staff 3
1 -10 -2 -7
2 -9 -4 -6
3 -4 -5 -2
4 -8 -6 -5
5 -7 -4 -8

So what i need it to do is on a week by week basis, tell me who has the highest amount refunded, something like this:

Top refunders week 1
1 Staff 1 -10
2 Staff 3 -7
3 Staff 2 -2

But for example, I then need to have the same for Week 5

Top refunders week 5
1 Staff 3 -8
2 Staff 1 -7
3 Staff 4 -4

I can make it pull through the numbers by using:
=SMALL('Summary - Refunds by User'!$F$44:$AM$44,B27)

But I can't seem to pull through the corresponding staff name?

 

[shemayisroel]

Hi,


Is what your looking for similar to the below?

http://www.mrexcel.com/forum/showthread.php?t=482848&page=2

Post # 12 by Aladin