What is the VLOOKUP function? Functions of the VLOOKUP . function

Excel’s VLOOKUP function is an extremely useful tool, so anyone who uses Excel wants to learn more about this VLOOKUP function. Don’t worry because today GhienCongListen will find out together with you What is VLOOKUP function? and its function. Rest assured, learning to use this function is much easier than you think!

What is the VLOOKUP function?

Basically, VLOOKUP allows you to search for certain information in a spreadsheet. For example, if you have a list of products with a price, you can find the price of a certain product in the list.

Advertisement

We will use the VLOOKUP function to find the price of the Photo frame product in the spreadsheet below. You’ve probably already seen its $9.99 price tag, but that’s just because this is a simple example. Once you have learned how to use VLOOKUP then you will be able to apply it to much larger and more complex spreadsheets, and that is when it will become really useful.

Advertisement

We will add the formula to the cell F2, but you can add a formula to any blank cell. Any formula should start with an equal sign (=), then type the name of the formula. Our argument will need to be enclosed in parentheses, so a parenthesis will need to be typed in. Now it will look like this:

=VLOOKUP(

Advertisement

Add arguments

Now we will add our arguments. The argument tells VLOOKUP what and where to look.

The first argument is the name of what you’re looking for, in this case Photo frame. Because the argument here is text, we need to enclose it in quotes:

=VLOOKUP(“Photo frame”

The second argument is the range of cells containing the data. In this example, our data is in the range A2:B16. As with other functions, you need to use commas to separate each argument:

=VLOOKUP(“Photo frame”, A2:B16

It’s important to know that the VLOOKUP function will always look for the first column in this range. In this example, it will look for “Photo frame” in column A. The output value (in this case, price) will always need to be to the right of that column.

The third argument is the column number index. It sounds like it, but it’s not too complicated: the first column in the data range is 1, the second column is 2, etc. In this case, we need to find the product price, and that price is in column 2. This means that our third argument is 2:

=VLOOKUP(“Photo frame”, A2:B16, 2

The fourth argument has a function that tells the VLOOKUP function to find a relative match, and can be TRUE or FALSE. If TRUE then it will find a relative match. Usually, it’s only useful when the first column contains a sorted numeric value. Since we are only looking for exact matches, the fourth argument should be set to FALSE. This is the last argument, so just close the quotes:

=VLOOKUP(“Photo frame”, A2:B16, 2, FALSE)

It is done! When you press Enter then it will output 9.99.

How the VLOOKUP . function works

Let’s take a look at how this formula works. It will first search in the first column vertically from top to bottom (VLOOKUP stands for Vertical lookup). When it finds “Photo framesit will move to the 2nd column to find the price.

As mentioned above, the price needs to be to the right of the product name. VLOOKUP cannot find a value to the left of the column it is looking for.

If we want to find the price of another product, we just need to change the first argument:

=VLOOKUP(“T-shirt”, A2:B16, 2, FALSE)

Or:

=VLOOKUP(“Gift basket”, A2:B16, 2, FALSE)

However, if you have to manually adjust the VLOOKUP formula every time you want to find the price of each product, it is very tiring. In the next example, we will show you how to use cell references.

You may need to know:

Another example

Ready to go through a slightly more advanced example? We’re going to change the spreadsheet a bit to make it look more realistic.

In the previous example, we typed the product name directly into the VLOOKUP formula. But in practice you need to use cell references. In this example, we will type the product name in the box E2, and then our VLOOKUP formula can use the cell reference to find information about that product. After that, we just need to type the new product name in the box E2 to find any product we want.

We’ve also added a 3rd column that contains a list of goods. Doing so will give us the option to find the price or the product type. Now our spreadsheet will look like this:

Our formula will be similar to the previous example, but we will need to change the first 3 arguments. Let’s start by changing the first argument to the cell reference (make sure you remove the quotes).

=VLOOKUP(E2, A2:B16, 2, FALSE)

To find the product category, we need to change the second and third arguments. First, we will change the range to A2:C16 to include the 3rd column. Next, we will change the index number column to 3 because our product catalog is in the 3rd column:

=VLOOKUP(E2, A2:C16, 3, FALSE)

When you press Enter, you will see the product Gift basket located in the list Gifts.

If you want to find another product’s category, just change the name of the data in cell E2:

Now you know what the VLOOKUP function is and how to use it in different situations. If you have any questions or suggestions, please leave a comment below for GhienCongListen to know!

According to edu.gcfglobal.org