INDEX MATCH, V or XLOOKUP, or INDEX with OFFSET?

[Tomeegee]

By the title, you've guessed I'm struggling to find the right path for what seems pretty basic. But trying to adopt the formulas from the posts and the videos leads me to #REF! despair!

The attached Workbook has been reduced to the important parts: A worksheet called "Pine Lakes Front" and another worksheet called "Scores for posting". This is part of a larger scoring workbook. The idea is that when I post a score on worksheet Pine Lakes Front, worksheet Scores for posting will populate the row "Score" under the appropriate Player (1-8). As pairings are random, a player can end up anywhere down the column. So I need to set this up so excel looks down Column B on Pine Lakes Front, finds the appropriate player, and imports the range Cxx-Lxx into worksheet Scores for posting. I know how to find the Column B entry with Index and Match, or VLookup or XLookup, but I can get the second part which has to find their appropriate score and place it.

Looking to learn! Thanks so much.

 

[steve the fish]

So long as you have consistent space between rows then you can use something like:

=INDEX(Sheet1!$C$1:$K$1000,MATCH(B3,Sheet1!$B$1:$B$1000,0)+8,0)

 

[Tomeegee]

Thanks for that. Some clarifications in case I'm missing something. (BTW, I tried to paste XL2BB data but just for these small screen shots the code was so long I went back to these. I'd welcome a way to send you the workbook).

I pasted the formula =INDEX(Sheet1!$C$1:$L$1000,MATCH(B3,Sheet1!$B$1:$B$1000,0)+0,0) in Cell 3 and =INDEX(Sheet1!$C$1:$L$1000,MATCH(B3,Sheet1!$B$1:$B$1000,0)+12,0) in Cell C7 (manually changing the +0,0 to +12,0 to match the proper row down).

The part that I'm not getting, and I don't think it's part of the formula, is matching the "Player #" to their corresponding score. You see below where the formula is drawing data from are the entries for Player 5 and Player 3. On the full worksheet Player 1 is all the way down at B57.

What I was trying to employ either V or XLookup for was to find "Player 1" on Sheet1, Column B, (in this case he's in B57) and count down from there to Score (8 rows) and populate the Score (Column C:L) from the row from Sheet1 to the Player 1's nine hole score on Sheet2.

The reason for this formula is that the match ups between players is randomized. From one game to the next, Player 1 and every other player will end up in different spots.

So for these screenshots, the formula would be searching for Player 2 match their scores.

I hope that makes sense!

 

[steve the fish]

The B3 in the formula I provided needs to point to the players name in the sheet. The Sheet1 in what I provided needs to be changed to the real sheet name.

 

[Tomeegee]

The B3 in the formula I provided needs to point to the players name in the sheet. The Sheet1 in what I provided needs to be changed to the real sheet name.

I changed the sheet name to match Sheet1 for ease. As for B3, do you mean, =INDEX(Sheet1!$C$1:$L$1000,MATCH(B3="Player 1",Sheet1!$B$1:$B$1000,0)+8,0)? I get an N/A. This is where I'm really daft. Not sure how asking B3 on Sheet1, which is Player 5 at the moment, skips down to find Player 1 on B57 and returns their score.

 

[steve the fish]

No. It will look for whatever you have in B3. Be it player 1 or player 5 or whatever.

 

[Tomeegee]

No. It will look for whatever you have in B3. Be it player 1 or player 5 or whatever.

Got it! Thanks so much Steve.