Vlookup mulitple columns

[Paul Bray-Boyce]

Hello everyone,

I have an interesting issue, that I have been playing with for several weeks. I have a golf match up schedule. I have everything working except when it is time to move the players score to the score sheet.

players are matched up by week. Week 1, Week 2, etc... One I have recorded all the players scores and points. I want to move the Scores to another sheet, as I mentioned above. I am doing a Vlookup by the persons name then pulling the 7th Column. which works perfectly =VLOOKUP(B3,wk2flt1R,7,FALSE) My issues is when Players Name in B3 does not appear on the right left side of the Schedule.

I thought I could use a nested Vlookup but doesn't appear to be working. as I would have expected. this is what I tried.
=VLOOKUP(VLOOKUP(B3,wk2flt1R,7,FALSE),wk2flt1L,7,FALSE) when the name is not found in the range I want to look in the second range then pulled the information from the 7th column from where the name is found.

In "week 1 Sheet" Player 2 would be in A3 if the name is found the lookup would pull the points total from G3 and place it in the Standings sheet next to player 2's Name. Player 1 is playing against player 2 Players 1 information is located in the "Week 1 sheet" but is listed in Column I3 and the information that needs to be copied is in Column O3

The goal is from the "Standings" sheet have the Vlookup look for the players name then return with his or her score. regardless of where the name is located in the Week 1 Sheet.

I hope this make sense. Any help is greatly appreciated.

 

[David Evans]

I take it that this is a Medal Play scenario that you are scoring, so why not just keep all of your results in a single table and filter/pivot out the results in whichever way you need? The only challenge I can visualize is the players handicap varying, but other than that, it should be relatively straightforward. (Even that can be overcome)

Keep a results table with Player/Gross Score/Handicap/Date/Points - Net Score can be a calculated field.

I think you can produce a simpler format that will not require the complex lookups

If you're stuck with your current format, you'll need to explain the difference between the left and right side of the schedule, as they are not alike and appear to be a different scoring system, based on the two pts columns ....

 

[Paul Bray-Boyce]

thanks for the reply, the sheets labeled Week X are who is playing who.. Player1 -vs- Player 2. this page also displays the players handicap so they know how many strokes they are giving or getting. So if you and I were playing against each other in week 1 the matchup / "Week 1" sheet would look like the image attached.
thanks again

 

[David Evans]

Aha - so you have a Match Play scenario?
How do you allocate points for a win? How do I get 5.5 points in that matchup?
Did you base your Excel sheet on a prior manual/paper process?
Are these scores for 9 holes? Otherwise you've got some sand-bagging low handicappers ...

Sorry to ask you so many questions, but I'm trying to understand the underlying mechanism - I'm also an avid golfer, but this is a new scoring system for me!

 

[Paul Bray-Boyce]

Scoring is based on your couple of things. you get 1 point for winning your match. you also get flight points based on the number of people that you beat in your flight. Since there are 10 people in flight 1 if you had the lowest score you would get a 9 because you beat 9 other people. when there is a tie, lets say we took 1st and 2nd this week but due to my HCP the scores are tied 38 & 38. we would take the flight points in this case 8 & 9 then divide it by 2 and we both get equal points for the week. 17 / 2 =8.5

I am the new score keeper for the league and I am working on moving away from paper and pencil into excel. there is still a lot of work to be done. but I just keep trying to tackle one problem before moving to the next. thanks

 

[Nebu]

Hi:

Is this what you are looking for?

I have constructed the formula assuming that you will be having only 2 set of data sets and all the tabs will be identical. The formula is dynamic as long as your tab names equals your week headings. You can simply drag the formula for rest of your weeks.

Thanks

 

[Paul Bray-Boyce]

Thank you... that worked, I got Vlookup stuck on the brain.. and failed to look at other options.. Thanks again