# Thread: Lookup formula not returning the correct result

1. ## Lookup formula not returning the correct result

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

2. 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)`

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

4. Originally Posted by Aussiebear
Should the ...Match(I\$2,... have been ....Match(C\$2,....
Yes

5. Thank you for your efforts here in this matter.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•