Vlookup + Hlookup combination

JZ729

New Member
Joined
Mar 11, 2018
Messages
4
I am new to the site as of today. :) I am building out an excel for a golf league and I have ran into a lil problem. What I am trying to do is pull in the handicap for each hole. I have succeeded with the formula. But the #1 handicap hole is referencing the vlookup name (which is 5 for the handicap). Instance if I have a 5 handicap in the league the output should be -1 for the first 5 hardest holes. It is skipping over the 1 handicap hole and just putting in 5 as what the handicap is and giving the 6th handicap hole a -1.

All your help would be much appreciated in helping me figure out what I am doing wrong.


The formula I am using is:

=VLOOKUP($AS$10,Sheet1!$A$4:$J$21,HLOOKUP(AT9,AT9:BB9,1,FALSE),FALSE)



Here is a glimpse of what my excel sheet shows:

[TABLE="width: 425"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 9"]Marsh Oaks (back)[/TD]
[/TR]
[TR]
[TD]Hole[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]Par[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Handicap[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]6[/TD]
[TD]9[/TD]
[TD]7[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]JZ[/TD]
[TD="align: right"] 5[/TD]
[TD] -1[/TD]
[TD]0[/TD]
[TD]-1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]-1[/TD]
[TD]5[/TD]
[TD]-1[/TD]
[TD]-1[/TD]
[/TR]
</tbody><colgroup><col span="2"><col span="6"><col><col span="2"></colgroup>[/TABLE]



Thanks again for all your help!

-John
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Hi John,

Welcome to the site.

A couple if things that might help you and others :-)

1. Thanks for putting up your sample, but without Row and column references on your sample people still have to guess exactly what is what between your formula and your data sample. :)

2. The following part of your formula :
Code:
[COLOR=#333333]HLOOKUP(AT9,AT9:BB9,1,FALSE)[/COLOR]
will always return the value in cell AT9 because by definition the value in cell AT9 will ALWAYS be found immediately when searching for it in cells AT9 -> BB9.

So effectively your VLOOKUP is actually saying :
Code:
[COLOR=#333333]=VLOOKUP($AS$10,Sheet1!$A$4:$J$21,AT9,FALSE)[/COLOR]
which means the column number returned from your data Table from your VLOOKUP will always be the column number contained in cell AT9.

I expect that should set you on the path to solve your overall problem ! :cool:

Cheers,
Warren K.
 
Upvote 0
Will it only be based on 9 holes?
What is the max handicap a person could have?
 
Upvote 0
Thanks Warren on your insite! When i have some free time this week i will look into what you stated and reply back.
 
Upvote 0
Ahoy- yes it is based only on 9 holes. I set the max handicap to be 14 in the sheet. I will find out more on Wednesday after we have our league meeting.
 
Upvote 0
Maybe something like this??
Copy formula down and across as needed.
Excel Workbook
ABCDEFGHIJK
1Hole101112131415161718
2Par435443454
3Handicap386975124
4JZ5-10000-1-1-1-1
514-2-1-1-1-1-2-2-2-2
68-1-1-10-1-1-1-1-1
711-1-1-1-1-1-1-2-2-1
Sheet
 
Upvote 0
Ahoy,

Thank you so much!

That works perfectly. That was the last piece I needed for the whole excel book to give me everything I needed from the handicap, Points per person each week and down to the # of skins each person won and how much they won.

This will take me 10 minutes now opposed to 1 to 2 hours a week to figure out everything for the league!

Very much appreciated!

-John
 
Upvote 0
You're welcome. Glad that worked for you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top