VLOOKUP / HLOOKUP / Top 10 List

**joker021971** · 10-06-2013, 11:54 AM

Attached is a spreadsheet I'm using to track winnings and fantasy points for my fantasy football league. I'd like to add a Top 10 Weekly Performance section for each category (Total FPS, QBs, RBs, WRs, TEs, DSTs), but I'm having no luck with HLOOKUP and VLOOKUP - two functions I thought I understood. I'm using LARGE to grab the top 10 scores in each category, but using HLOOKUP to add the week value and using VLOOKUP to add the coach value is not working. Maybe there is a better way or maybe I'm missing something about the look up functions? Any suggestions would be greatly appreciated.

**benishiryo** · 10-06-2013, 08:53 PM

welcome to the forum. try these array formulas. in X18:

Formula:

Please Login or Register  to view this content.

in Y18:

Formula:

Please Login or Register  to view this content.

or if it always starts in Week 1, it can be shorter like:

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

**joker021971** · 10-07-2013, 12:31 PM

Thanks for the welcome and the amazing solution. I admit I still don't fully understand these formulas as I only had a brief opportunity to review (i.e. copy and paste and be amazed) this morning, but I think we're on the right track. I will be spending more time with these formulas, so I better understand what's happening.

I used the second formula for the week calculation and found no issues.

Initially the coach calculation had a small issue when there were tie values, but I was able to resolve. I still think there will be an error if there are ever tie values in the same category, but with this change to the X18 formula the issue is resolved for now (removed the $ from the Z18 reference in the COUNTIF function):

Formula:

Please Login or Register  to view this content.

I will be adding the results for week 5 and updating in the next 24 hours, so I'll post a new version early tomorrow and, hopefully, mark this thread as solved.

Thanks again!
Marvin

**joker021971** · 10-14-2013, 08:01 AM

Sorry for the delay! Thanks again! This is working as expected and I even understand (after some additional reading) what its doing! Attached is the latest version. On to other improvements...