How do I offset the return cell of a vlookup formula

[IronDogg]

Hello,

In the attached spreadsheet, I have two sheets. One sheet is a weekly score tracking for a golf season, the other sheet is an ongoing calculation and record of a player's average.

What I am trying to accomplish is I want to look up the player's current average after week 4 results in the handicap sheet and insert it into the week 5's sheet under column W and depending on the players name I enter in column B.

The formula I was trying to make use of is:
=IFERROR(VLOOKUP($B5,'2015_Handicaps'!A4:Q25,6,FALSE),"")

Of course that doesn't work. It returns a value on the same row as the lookup value, whereas I want to return the value directly below in the next row. So for player 1, the value returned in this formula is "45". I would like a formula to return "55".Excel Formula Question 2.xlsx

[TMS]

Maybe use INDEX/MATCH instead of VLOOKUP. You can use the MATCH to get the row and add 1 to it for the INDEX.

=IFERROR(INDEX('2015_Handicaps'!$F$4:$F$25,MATCH($B5,'2015_Handicaps'!$A$4:$A$25,0)+1),"")


Regards, TMS

[rcm]

The usage of the functions match and indirect make your request work. They are necessary locate the exact row and column from where to extract the data.

Please note that the week number is necessary. I placed it in cell A1.