Vlookup + Hlookup combination

[JZ729]

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

 

[tdh777]

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 :

[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 :

[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 !

Cheers,
Warren K.

 

[AhoyNC]

Will it only be based on 9 holes?
What is the max handicap a person could have?

 

[JZ729]

Thanks Warren on your insite! When i have some free time this week i will look into what you stated and reply back.

 

[JZ729]

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.

 

[AhoyNC]

Maybe something like this??
Copy formula down and across as needed.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Hole		10	11	12	13	14	15	16	17	18
2	Par		4	3	5	4	4	3	4	5	4
3	Handicap		3	8	6	9	7	5	1	2	4
4	JZ	5	-1	0	0	0	0	-1	-1	-1	-1
5		14	-2	-1	-1	-1	-1	-2	-2	-2	-2
6		8	-1	-1	-1	0	-1	-1	-1	-1	-1
7		11	-1	-1	-1	-1	-1	-1	-2	-2	-1
Sheet

 

[JZ729]

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

 

[AhoyNC]

You're welcome. Glad that worked for you. Thanks for the feedback.