Consulting

Results 1 to 5 of 5

Thread: Lookup formula not returning the correct result

  1. #1
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,179
    Location

    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
    Attached Files Attached Files
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,617
    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)
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,179
    Location
    Thank you P45cal, however can you explain the second version? Should the ...Match(I$2,... have been ....Match(C$2,....
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,617
    Quote Originally Posted by Aussiebear View Post
    Should the ...Match(I$2,... have been ....Match(C$2,....
    Yes
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,179
    Location
    Thank you for your efforts here in this matter.
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Posting Permissions

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