Skip to content

VLOOKUP and Two Practical Uses

One of the most used and popular functions in spreadsheets is the VLOOKUP function.

In this post, we cover how the VLOOKUP function works and show two examples of how this function can be used in real life.

What Does VLOOKUP Do?

The VLOOKUP function is most commonly used to lookup a value that relates to some other value. Now what in the world does this mean?

Let’s say that you have a list of food items and a list of prices (one price for each food item). You know the food item you are interested in, but you do not know the price. This is where VLOOKUP would come in handy.

You would use VLOOKUP to “lookup” the price of any specific food item. In other words, you are looking up the price value that relates to the food item value.

So if you want to know how much an apple would cost, you would use VLOOKUP to find the value of “apple,” and then the function would return the price (relating value) for that apple.

VLOOKUP - Find the price of apple

How Does VLOOKUP work?

VLOOKUP is a function that takes four arguments (inputs to the function). These arguments are as follows:

=VLOOKUP(lookupValue, searchRange, columnNumber, isSorted)

Here is what each argument represents:

  • lookupValue – This is the value you are using as a reference point to lookup another value. In our previous example, the “apple” would have been our lookup value.
  • searchRange – This argument is the range or table that the function will look in to find both the lookup value and the relating value. Both of these values must be present within this range.
  • columnNumber – This is the column that the function will look in to find the relating value that the function is trying to return to us. Looking back at our previous example, if the list of prices were to be in column 3 of our search range, then the value for this argument should be the number 3.
  • isSorted – This argument can be one of two values – either TRUE or FALSE. If the values you are searching through are sorted in ascending order, then you can put TRUE. If the values are not in order, then use the value of FALSE.

Now that you are familiar with the arguments, let’s look at the function in action. We will use our previous example to show how the function works.

Take a look at the range shown below:

The VLOOKUP Range

In this range, there are three columns. One for the food items, one for the vendor names, and one for the prices. This range will be our search range (the second argument) of our VLOOKUP function.

There are two important details to take note of about our search range.

  1. The value we will be looking for (our designated food item in this case) MUST be in the first column of our range. It cannot be in the second column, third column or any other column. The search value MUST always be in the first column.
  2. Each column in our search range must be of equal size. In other words, the range that we use as our search range must be a rectangle. We cannot have some odd shaped range where one column is 20 cells while the others are 10 cells and 15 cells. Each selected column for our overall search range must have an equal number of cells selected. If you are still confused, see the image below.

Acceptable VLOOKUP Range

Going back to our example, we want to know the price of our chosen food item, which in this case is “apple”. We also know that the prices of the food items reside in column 3 of our range.

Column 3 of the VLOOKUP Range

Finally, we take a look at our food items (the values we are searching through) and we see that they are not in any particular order and certainly not in ascending order. This being the case, we know that our last argument (isSorted) should be set to FALSE.

We now have all of the arguments for our VLOOKUP function ready to go.

Our final function should look like this:

=VLOOKUP(C13,B3:D11,3,FALSE)

VLOOKUP - Finished Example

If you are still confused or have any questions, please feel free to leave a comment down below. Also, if you want to work with this exact example right on your own computer, download the example spreadsheet below and try it out for yourself.

Vlookup-Example

Two Practical Uses

Now that you know how VLOOKUP works, here are two practical uses for the VLOOKUP function that can apply to real life.

Example No. 1 – Looking Up Password Information

Let’s say that you store a variety of passwords and account information in a spreadsheet.

Of course, you can always look through the spreadsheet to find the information when you need it, but there is a better way. You can use the VLOOKUP function instead.

Using the VLOOKUP function, you can search through the account information stored in the spreadsheet and have it return all of the information you are looking for.

For instance, if you store all of the account names in the first column, you can use the VLOOKUP function to search for any account name, and the function can immediately lookup and show you the password that is stored for the account name.

How awesome is that?

Also, if you like this idea but don’t want to build this spreadsheet yourself, try out the Spreadsheet Life Password Keeper. This is a free excel template that is pre-built and ready to go.

Example No. 2 – Logging your Books

In this example, let’s pretend that you have a spreadsheet that keeps a record of all the books you read. And if you don’t have one, check out the Spreadsheet Life Reading Tracker for free!

Anyway, what happens if you read the same book more than once? Do you want to be forced to type the same book’s information over and over again?

Well…you don’t have to. If you store the book’s information the first time you read it, you can then use the VLOOKUP function to search for the title and return all of the other information for you.

No more typing the same information over and over again. Use VLOOKUP to save you both time and effort in the long run!

A VLOOKUP Video

If you are the type of person who enjoys learning through video, check out our VLOOKUP tutorial video below.

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top