Lookup formula not returning the correct result

[Aussiebear]

Just fooling around with a Golf Handicap calculator and having trouble with a Lookup formula not returning the correct result. On sheet 1, the relevant hole numbers run C2: K2 and the variable value in C9 is the handicap value of the player. On sheet 2 are two named tables (tblMatchIndex and tblStokeIndex) which I use to return the handicap value per hole.

In cell C11 I was trying to use the formula =VLOOKUP($C$9,tblStrokeIndex,Match(C$2,$C$2:$K$2,0),0) but this causes a returned value of 20 because the Match(C$2,$C$2:$K$2,0) is causing the lookup to look in Column 1 of the table (C$2 value is 1 representing the Hole#). It needs to look in Column 2 not Column 1.

Tried Match((C$2+1),$C$2:$K$2,0) but this fails when reaching cell K2 with a #N/A error

[p45cal]

try:

=VLOOKUP($C$9,tblStrokeIndex,MATCH(C$2,Sheet2!$B$42:$S$42,0)+1,0)

or sticking with named ranges:

=VLOOKUP($C$9,tblStrokeIndex,MATCH(I$2,INDEX(tblStrokeIndex,1,0),0),0)

[Aussiebear]

Thank you P45cal, however can you explain the second version? Should the ...Match(I$2,... have been ....Match(C$2,....

[p45cal]

Should the ...Match(I$2,... have been ....Match(C$2,....

Yes

[Aussiebear]

Thank you for your efforts here in this matter.