Still feeling nervous for your exam? Check out the Excel Cram Kit (Exam 2) for practice modules that emulate the difficulty that you'll face on your exam. Each module includes a thorough step-by-step explanation where I walk you through the entire problem solving process!
Remember our Valentine's Day example in VLOOKUP | Excel in 30 Minutes (Test #1)? If not, here you go:
"Let's say Valentine's Day is coming up and you're the rare mix between a chick-magnet and a spreadsheet-nerd. Since you've got absolute game, you've been talking to a good amount of girls recently. With the holiday coming up, you want to pick a special someone to give flowers to and take out to dinner.
Your mixture of flirtatious skills and Excel wizardry has enabled you to create a spreadsheet of each of these girls you're talking to with each of their favorite flowers."
If you remember, within the VLOOKUP example we had a vertically oriented spreadsheet of our love interests...
...and decided to take Lilly out on a date.
Unfortunately, Lilly found out that you are a psychotic freak and sort your love interests on a vertically-oriented list on a spreadsheet. So, she decided to go out with Brad instead. Darn it.
After Lilly's denial, you cannot bear to look at a VLOOKUP table anymore. We have to find a new date for you, and we'll use our HLOOKUP table to do so.
The only difference between VLOOKUP and HLOOKUP is this: whether or not the list is oriented vertically or horizontally.
Now, each of the girls we're talking to is in a horizontally-oriented table instead of a vertically-oriented table!
Coding a HLOOKUP function
Let's say we decide to ask Carly out on a date. Notice how Carly has an ID of 819...
...so let's place that in C5.
Now for our HLOOKUP template:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Our lookup_value is going to be C5, because that's where we placed the ID of Carly.
=HLOOKUP(C5, table_array, row_index_num, [range_lookup])
The table_array is going to be A1:F3, since that's where our horizontally-oriented table is contained.
=HLOOKUP(C5, A1:F3, row_index_num, [range_lookup])
For our row_index_num...
The row_index_num argument determines which row you want values to be shown for in the result.
Therefore, our row_index_num is going to be 3, since the 3rd row contains the flowers that each girl likes.
=HLOOKUP(C5, A1:F3, 3, [range_lookup])
[range_lookup], like with VLOOKUP, is going to be FALSE. We want to search for exact ID matches, not approximate.
=HLOOKUP(C5, A1:F3, 3, FALSE)
When we type this into Excel, we figure out that Carly like's Tulips!
Let's say you're a cashier at your local grocery store. In this role, you need to memorize the codes on all the stickers and the items they represent.
Given the following spreadsheet, determine how to set up a lookup function (either VLOOKUP or HLOOKUP) in cell B5 to help you practice memorizing the codes.
Excel (Exam 2) Follow-along Guide
It's no secret you retain info better when you write it down. That's why I created the Excel (Exam 2) Follow-along Guide for you!
As you come upon key concepts highlighted in yellow, like this sentence here, you can fill-in-the-blanks on your Follow-along Guide so that you remember all the important stuff for later!
You can obtain the Excel (Exam 2) Follow-along Guide for FREE by entering your email below!
Next, let's learn about the IFERROR function!
Fill-in-the-blanks for highlighted, key points in the concepts below!
Step-by-step walkthrough for each of the questions you need to be ready for!
"I got this review for my 2nd CSE exam after not doing so well on the first one, and it was so helpful! Will definitely be using again in the future."
Oct. 26, 2021