Microsoft Excel Tutorial:
Sean is calculating scores for a golf league. The Differential is Score minus Rating times 113 divided by the Slope. Given a course name and set of pins, he needs to lookup into a table to get the rating and slope.
Download the workbook from: Golf_Differential_Two_Way_Lookup_3_Ways_2368.xlsx
This video shows three easy ways to do the two way lookup:
FILTER function in Excel or Google Sheets
XLOOKUP function in Excel
Power Query in Excel (the best and easiest way)
Table of Contents
(0:00) Intro to video
(0:40) Calculating a Differential
(1:07) Solving with a FILTER function in Excel
(4:30) Solving with a two-key XLOOKUP
(5:20) Solving with a merge in Power Query
(7:48) Wrap-up
Sean is calculating scores for a golf league. The Differential is Score minus Rating times 113 divided by the Slope. Given a course name and set of pins, he needs to lookup into a table to get the rating and slope.
Download the workbook from: Golf_Differential_Two_Way_Lookup_3_Ways_2368.xlsx
This video shows three easy ways to do the two way lookup:
FILTER function in Excel or Google Sheets
XLOOKUP function in Excel
Power Query in Excel (the best and easiest way)
Table of Contents
(0:00) Intro to video
(0:40) Calculating a Differential
(1:07) Solving with a FILTER function in Excel
(4:30) Solving with a two-key XLOOKUP
(5:20) Solving with a merge in Power Query
(7:48) Wrap-up
Transcript of the video:
Learn Excel from MrExcel podcast episode 2368.
Calculating a golf league differential which involves a two-way lookup.
Three different ways.
He welcome back to the MrExcel netcast. Today's question is sent in by Sean.
He is trying to automate a golf league scoring workbook.
The issue is that we need to find a course rating given a course and a set of pins.
It is essentially a two-way lookup.
This video is going to show you three different ways to solve this.
The first one is the FILTER function.
And then the XLOOKUPfunction.
And then the absolute easiest way to do it, with a Power Query merge.
So, here is our issue.
Sean has Player, Which Week, Course, and what set of pins or tees they shot from.
And then their score. He wants to calculate a differential.
Which is easy. It is (Score - Rating) * 113 / Slope.
So all I need to do is pull in the rating and the slope for this course and these pins.
Here is what the courses looks like.
We have Course, which Tee, the Rating, and the Slope.
Alright, now. I am going to start with FILTER.
Which is the worst way to solve this. But.
Someone in the league is not using Excel. They are using Sheets.
And FILTER is in Sheets. So here we go. So, =FILTER( What do we want to filter? We are looking for the Rating.
I am going to go to the courses and choose the Rating column.
Comma. And then the next thing that we want to know.
is what is the filter?
So, for filter, I am going to say that I want to look at Course and Pins.
So come back here to Courses worksheet.
And go over to Column A.
Choose all of the courses.
Is equal to.
And then on my data tab, I will choose the course.
That is how we would filter to find just the course.
But we also have to filter by two things; the course and the pins.
That means I am going to take this criteria and wrap it in parentheses like this.
And then I am going to multiply, because multiply is like an AND function in Excel.
And then the second criteria.
Go look at the Courses worksheet and the Tee field.
And say is that equal to.
The pins?
Close the criteria.
Close the FILTER.
That should return the rating for Rio Hondo Blue.
Double-click to copy this down.
Ugh. OK. These #CALC! errors.
The #CALC! error says we have an empty array. In other words, nothing is matching the FILTER.
Let's go look for Lakewood White.
To see what the deal is there.
So I come back here to the Courses worksheet.
Search for Lakewood.
Ha. OK. So, Sean.
Lakewood and Lakewood Country Club aren't seen as a match.
If it is in this table as "Lakewood Country Club" then it needs to be in here as "Lakewood Country Club".
But since I will be doing this again with XLOOKUP and then Power Query.
My decision is going to be to change this from Lakewood Country Club to Lakewood to match the data in Scores.
Either way is going to work.
And then the other #CALC! error that we have is La Costa Legends, Championship pins.
[ typing ].
Here it is called the "Champ" tee.
So we will take our "Championship". Ctrl+C. Back here to courses. Paste like that.
So it is important to make sure that the course name and the pins match exactly.
For "Champ" and "Championship", Excel does not see that as a match.
It would be easy to take this formula and Ctrl+C.
Notice that I copied it from the Formula Bar.
Start a formula and Paste.
We don't want the Rating.We want the Slope.
[ typing ].
There is our differential, all working without having to look it up one at a time, manually.
Now that is the FILTER function.
The way I would prefer to do this is with XLOOKUP.
It seems more straightforward to me.
What are we looking up? We are looking up the combination of Course ampersand Pins.
Where are we going to look?
We are going to look on the Courses tab.
Choose Course ampersand pins.
What do we want to return?
We want to return the Rating.
Close Paren. Press Enter. There is our Rating.
To get the Slope.
Just edit Rating and change to Slope like that.
[ typing ].
Perfect. Double-click to copy that down.
Because I fixed the names on the previous calculation, it is all going to work here.
So both formulas are fairly straightforward.
But really, the way to go would be to run this through Power Query.
Power Query would make this tremendously easy.
Come back here to Courses.
Add this data to Power Query.
Choose Data, From A Table or Range.
The query name is going to be Courses. All you have to do is Close and Load To...
Choose "Only Create a Connection".
Click OK.
And then come here to the Scores data.
From a Table or Range.
That will be called Scores. That is perfect.
Close and Load. Close and Load To.
Only Create Connection.
Now that we have those two items: Courses and Scores available.
We just come to any blank area.
Get Data. Combine Queries. Merge Query.
Start from the Scores query.
Link to Courses.
The connection between the two will be course and then pins.
Click on Course. Control+Click on Pins.
See the 1 and the 2 there?
Down here, click on Course and then Tee.
So the 1 and the 2.
We want all from the first and matching from the second.
Click OK.
We have Name, Week, Course, Which Pins, the Score.
And then from Courses...
We want the Rating and the Slope.
We don't need the original column name as the prefix.
Now that we have all this data, choose Add Column and Create a Custom Column.
Call it Differential.
It is (Score minus Rating).
Times 113 divided by the Slope.
No syntax errors. Click OK.
And there is the differential.
Home. This time, actually Close & Load which will bring the data back to the grid.
On a new worksheet, like that.
The beautiful thing. Next week when you get the new scores, simply enter them here.
at the bottom of this table.
If there is a new course, enter that information.
And then simply click the Refresh icon over there.
And it all will just work perfectly.
If it was me. If this was my league, I would be doing this in Power Query.
It would be so much less effort in the future.
If you like these videos, please Like, Subscribe, and Ring the Bell.
I want to thank Sean for sending that question in.
Sean, good luck on getting that last person to switch over from Sheets to Excel. It will be a lot easier.
I want to thank you for stopping by. We will see you next time for another netcast from MrExcel.
Calculating a golf league differential which involves a two-way lookup.
Three different ways.
He welcome back to the MrExcel netcast. Today's question is sent in by Sean.
He is trying to automate a golf league scoring workbook.
The issue is that we need to find a course rating given a course and a set of pins.
It is essentially a two-way lookup.
This video is going to show you three different ways to solve this.
The first one is the FILTER function.
And then the XLOOKUPfunction.
And then the absolute easiest way to do it, with a Power Query merge.
So, here is our issue.
Sean has Player, Which Week, Course, and what set of pins or tees they shot from.
And then their score. He wants to calculate a differential.
Which is easy. It is (Score - Rating) * 113 / Slope.
So all I need to do is pull in the rating and the slope for this course and these pins.
Here is what the courses looks like.
We have Course, which Tee, the Rating, and the Slope.
Alright, now. I am going to start with FILTER.
Which is the worst way to solve this. But.
Someone in the league is not using Excel. They are using Sheets.
And FILTER is in Sheets. So here we go. So, =FILTER( What do we want to filter? We are looking for the Rating.
I am going to go to the courses and choose the Rating column.
Comma. And then the next thing that we want to know.
is what is the filter?
So, for filter, I am going to say that I want to look at Course and Pins.
So come back here to Courses worksheet.
And go over to Column A.
Choose all of the courses.
Is equal to.
And then on my data tab, I will choose the course.
That is how we would filter to find just the course.
But we also have to filter by two things; the course and the pins.
That means I am going to take this criteria and wrap it in parentheses like this.
And then I am going to multiply, because multiply is like an AND function in Excel.
And then the second criteria.
Go look at the Courses worksheet and the Tee field.
And say is that equal to.
The pins?
Close the criteria.
Close the FILTER.
That should return the rating for Rio Hondo Blue.
Double-click to copy this down.
Ugh. OK. These #CALC! errors.
The #CALC! error says we have an empty array. In other words, nothing is matching the FILTER.
Let's go look for Lakewood White.
To see what the deal is there.
So I come back here to the Courses worksheet.
Search for Lakewood.
Ha. OK. So, Sean.
Lakewood and Lakewood Country Club aren't seen as a match.
If it is in this table as "Lakewood Country Club" then it needs to be in here as "Lakewood Country Club".
But since I will be doing this again with XLOOKUP and then Power Query.
My decision is going to be to change this from Lakewood Country Club to Lakewood to match the data in Scores.
Either way is going to work.
And then the other #CALC! error that we have is La Costa Legends, Championship pins.
[ typing ].
Here it is called the "Champ" tee.
So we will take our "Championship". Ctrl+C. Back here to courses. Paste like that.
So it is important to make sure that the course name and the pins match exactly.
For "Champ" and "Championship", Excel does not see that as a match.
It would be easy to take this formula and Ctrl+C.
Notice that I copied it from the Formula Bar.
Start a formula and Paste.
We don't want the Rating.We want the Slope.
[ typing ].
There is our differential, all working without having to look it up one at a time, manually.
Now that is the FILTER function.
The way I would prefer to do this is with XLOOKUP.
It seems more straightforward to me.
What are we looking up? We are looking up the combination of Course ampersand Pins.
Where are we going to look?
We are going to look on the Courses tab.
Choose Course ampersand pins.
What do we want to return?
We want to return the Rating.
Close Paren. Press Enter. There is our Rating.
To get the Slope.
Just edit Rating and change to Slope like that.
[ typing ].
Perfect. Double-click to copy that down.
Because I fixed the names on the previous calculation, it is all going to work here.
So both formulas are fairly straightforward.
But really, the way to go would be to run this through Power Query.
Power Query would make this tremendously easy.
Come back here to Courses.
Add this data to Power Query.
Choose Data, From A Table or Range.
The query name is going to be Courses. All you have to do is Close and Load To...
Choose "Only Create a Connection".
Click OK.
And then come here to the Scores data.
From a Table or Range.
That will be called Scores. That is perfect.
Close and Load. Close and Load To.
Only Create Connection.
Now that we have those two items: Courses and Scores available.
We just come to any blank area.
Get Data. Combine Queries. Merge Query.
Start from the Scores query.
Link to Courses.
The connection between the two will be course and then pins.
Click on Course. Control+Click on Pins.
See the 1 and the 2 there?
Down here, click on Course and then Tee.
So the 1 and the 2.
We want all from the first and matching from the second.
Click OK.
We have Name, Week, Course, Which Pins, the Score.
And then from Courses...
We want the Rating and the Slope.
We don't need the original column name as the prefix.
Now that we have all this data, choose Add Column and Create a Custom Column.
Call it Differential.
It is (Score minus Rating).
Times 113 divided by the Slope.
No syntax errors. Click OK.
And there is the differential.
Home. This time, actually Close & Load which will bring the data back to the grid.
On a new worksheet, like that.
The beautiful thing. Next week when you get the new scores, simply enter them here.
at the bottom of this table.
If there is a new course, enter that information.
And then simply click the Refresh icon over there.
And it all will just work perfectly.
If it was me. If this was my league, I would be doing this in Power Query.
It would be so much less effort in the future.
If you like these videos, please Like, Subscribe, and Ring the Bell.
I want to thank Sean for sending that question in.
Sean, good luck on getting that last person to switch over from Sheets to Excel. It will be a lot easier.
I want to thank you for stopping by. We will see you next time for another netcast from MrExcel.
