VLOOKUP | CSE 148 – Excel Exam 1

Still feeling nervous for your exam? Check out the Excel Cram Kit (Exam 1) 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!

VLOOKUP explained

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.

Within this spreadsheet, you've listed your love interests vertically.

Use VLOOKUP to look for a specific value within a row of a vertically-oriented table.

Coding a VLOOKUP function

Let's say you decide to take out Lilly and give her some flowers for your special Valentine's date. Using our vertically-oriented table, we can see she has an ID of 561.

Let's place her ID into cell F2, which represents the ID of the girl we want to get flowers for.

Something important to note here...

With VLOOKUP, the cell in which you're looking up must be the leftmost column in the table.

Now for our VLOOKUP function. Here is the template:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Our lookup_value is the value we want to find in the leftmost column of the table, which we just placed into F2. We can code that like so:

=VLOOKUP(F2, table_array, col_index_num, [range_lookup])

Concerning table_array, that is the table that we're referencing, which is contained within A1:C6.

=VLOOKUP(F2, A1:C6, col_index_num, [range_lookup])

col_index_num is a tough one to remember, so here it is in simple terms:

The col_index_num argument determines which column you want values to be shown for in the result.

Therefore, since we're wanting to see the girl's desired flower, we should put "3" in here, since the "Favorite Flower" column is the 3rd column in our table.

=VLOOKUP(F2, A1:C6, 3, [range_lookup])

Lastly, for [range_lookup] we're going to set it to FALSE, because we want an exact match of ID. If we set it to TRUE, it would only look for an approximate match and we could potentially buy the wrong flowers for our lovely date.

Use the [range_lookup] argument to determine whether or not you're looking for an exact match (FALSE) or an approximate match (TRUE). (In most cases, you will want to use FALSE.)

=VLOOKUP(F2, A1:C6, 3, FALSE)

When we type this into Excel, we figure out that Lilly likes Dandelions!

Practice Problem

Imagine you're at a basketball game and witness an absurdly sick dunk by #4. You don't know their name, so you check out the team roster for more information.

Given the spreadsheet, determine what the name of the player was with a VLOOKUP function.

=VLOOKUP(F1, A1:C12, 3, FALSE)

Animated GIF

Wait, why are we getting "N/A"?

Because we haven't filled in the value yet for the Jersey Number! Once we do so, we get the following result:

Animated GIF

The player you just witnessed dunk was "Ben Vance". Woohoo. Congratulations. Hope you took a picture, it'll last longer.

Excel (Exam 1) Follow-along Guide

It's no secret you retain info better when you write it down. That's why I created the Excel (Exam 1) 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 1) Follow-along Guide for FREE by entering your email below!

Next, let's learn how to calculate payments on loans and annuities with the PMT function.

Free Resources

Each exam concept broken down in simple, real-world examples!

ToolsExcel (Exam 1) Follow-along Guide
LessonYEAR, MONTH, DAY
LessonSUM & AutoSUM
LessonRelative vs. Absolute Cell References
LessonRANK
LessonCOUNT
LessonROUND
LessonWEEKDAY vs. WORKDAY
LessonVLOOKUP
LessonPMT
LessonIF
LessonPercent change
LessonExcel charts
LessonImporting text files

Practice Problems

Step-by-step walkthrough for each of the questions you need to be ready for!

"I bought this Cram Kit the night before my exam, and it helped me tremendously. It provides step-by-step explanations for all the units covered in class."

Matt Lamanna
Oct. 24, 2021

Rating: 5 out of 5.

Leave a Comment