VLOOKUP in different coumns

[Irish Pat]

Hi all,
I have attached a workbook and the last two tabs on this workbook are labelled TOTALS and LEADERBOARD. On the leaderboard sheet column A is just used to manually input postion on leaderboard (if this could be automated it would be a bonus), column B is a list of names and column C scores for week 1
On the TOTALS sheet column A is a list of names (corresponding to column B on leaderboard sheet) column B are the week 1 scores and I have a VLOOKUP formula in column C on leaderboard that updates this for me no problem.
The problem i have is that on the TOTALS sheet, week 2 totals are in column G while the names I need to reference are still the same ones in column A
Maybe looking at the workbook would help to explain it better as I am not very good with my explanations.
Many thanks in advance,
Pat

[scottylad2]

Hi, I've used an Index and Match rather than your Vlookup. you'll see if named a few ranges, makes putting the formula together a little easier You'll see also inn C2 D2 and E2 i've changed what you had in that cell for the purpose of getting a column match in the Index function

[Irish Pat]

Scottylad2,
Thank you very much for your help with that problem, thats exactly what I was looking for, just one other question, is there any way to automatically update the leader -board based on the result in the totals column F on the leaderboard sheet.
Many thanks,
Pat

[scottylad2]

I'm not sure I understand, in F3 in the leaderboard tab you have 810, which is the score which is the score for pos 1. are you looking for the names to be rearranged according to the rank? ie if that score wasn't the top score, say it was second top are you wanting the names to rearange automatically?

[Irish Pat]

thats exactly what i was looking for, so rather than have to manually sort the highest that it would be done automatically.
I always want the top names and top scores to be in descending order whenever the scores change, hope that makes sense.
Again thanks for your help,time & patience lol
Cheers,
Pat

[scottylad2]

I think maybe you'll need to wait in one of the VBA gurus taking that one on

[Irish Pat]

No prob Scotty, Thanks for your help on the other mate, if soemone picks it up well and if not I will keep doing my manual sort lol

[Irish Pat]

Scotty, Sorry for bothering u again, but I have run into a bit of a problem, when I add new members to the group I get loads of N/A errors, would you mind having a look at attached please. By the way the group will have a max membership of 80 people.
Cheers,
Pat

[scottylad2]

On my phone just now, my guess is the ranges on your index will need expanded. I'll look on tomorow and adjust if no one else has. A query from yesterday, are you manually sorting both the totals and the weekly results?

[Irish Pat]

Yeah scotty still doing manually and again thanks for ur help mate, its really appreciated.
Cheers,
Pat
ps. is it possible for you to explain how I would go about expanding the ranges in my index Scotty ?

[scottylad2]

I've reattached your sheet with a couple of changes. In the Totals page, i've wrapped your formula in an error handler, Iferror to be precise. THis will lose th #NA and replace any displaying with a 0. If you'd rather the 0 was omitted, then instead of seeing a 0 you could see a blank, change =IFERROR(INDEX(ResultsTable,MATCH($B3,GroupMembers,0),MATCH(C$2,WeekList,0)),0) to this, replace the 0 with "" like below
=IFERROR(INDEX(ResultsTable,MATCH($B3,GroupMembers,0),MATCH(C$2,WeekList,0)),"")

I've also expanded the ranges to go from where they were to 80 rows. To alter them further, go into the name manager, press Ctrl + 3 together to bring up the dialogoue box, highlight the named range in question, in this case ResultsTable and change the range to however many rows you require. Do likewise with GroupMembers, keep the size in Rows the same

hope this helps, still not came up with anything for your manual sort.

file attached in the following post

[scottylad2]

Forgot my file at last post lol

[Irish Pat]

Scotty really appreciate the time and effort you have put into this mate, great piece of work, hopefully this will keep me from annoying you for a while again lol

Cheers,
Pat