Many of our students have told us that they really want to learn how to use the VPR (VLOOKUP) в Microsoft Excel. VLOOKUP function is a very useful tool, and learning how to work with it is easier than you think. In this lesson, the basics of working with the function VPR chewed in the most accessible language that even complete “dummies” will understand. So let’s get started!
Before you start learning, you should understand the basics of how functions work. Pay attention to the section Formulas and Functions our tutorial for Microsoft Excel. VPR works the same in all versions of Excel, it even works in other spreadsheets like Google Sheets.
What is a VPR?
First of all, the function VPR allows you to search for specific information in Excel spreadsheets. For example, if there is a list of products with prices, then you can find the price of a certain product.
We will now find with VPR the price of the goods Photo frame. You probably already see that the price of the goods $ 9.99, but this is a simple example. Understanding how the function works VPR, You can use it in more complex spreadsheets, and then it will be really useful.
We will insert the formula into a cell E2, but you can use any free cell. As with any formula in Excel, we start with an equal sign (=). Next, enter the name of the function. Arguments must be enclosed in parentheses, so we open them. At this point, you should have something like this:
=VLOOKUP(
=ВПР(
Adding Arguments
Now let’s add the arguments. Arguments tell functions VPRwhat and where to look.
The first argument is the name of the element you are looking for, in our example it is Photo frame. Since the argument is text, we must enclose it in quotes:
=VLOOKUP("Photo frame"
=ВПР("Photo frame"
The second argument is the range of cells that contains the data. In our case, the data is in the range A2: B16. As with any other Excel function, you must insert a separator between the arguments (a comma in the English version of Excel or a semicolon in the version).
=VLOOKUP("Photo frame",A2:B16
=ВПР("Photo frame";A2:B16
It is important to remember that VPR always looking for first left column the specified range. In this example, the function will search in a column A value Photo frame. Sometimes you will have to swap columns to get the data you need in the first column.
Third argument is the column number. This is easier to explain with an example than with words. The first column of the range is 1, the second is 2 and so on. In our example, we want to find the price of a product, and the prices are in the second column. So our third argument will be the value 2.
=VLOOKUP("Photo frame",A2:B16,2
=ВПР("Photo frame";A2:B16;2
Fourth argument tells the function VPR, you need to look for an exact or approximate match. The argument value can be TRUE CODE (TRUTH) or FALSE (FALSE). If a TRUE CODE (TRUE), the formula will look for an approximate match. This argument can only have this value if the first column contains data sorted in ascending order. Since we’re looking for an exact match, our fourth argument will be FALSE (FALSE). This ends the arguments, so we close the brackets:
=VLOOKUP("Photo frame",A2:B16,2,FALSE)
=ВПР("Photo frame";A2:B16;2;ЛОЖЬ)
Ready! After pressing Enter, You should get a response: 9.99.
How does the VLOOKUP function work?
Let’s see how this formula works. First, it looks up the given value in the first column of the table, searching from top to bottom (vertically). When a value is found, for example, Photo frame, the function moves to the second column to find the price.
VPR – shorthand for Вvertical ETCinspection, VLOOKUP – from Vvertical LOOKUP.
If we want to find the price of another product, we can simply change the first argument:
=VLOOKUP("T-shirt",A2:B16,2,FALSE)
=ВПР("T-shirt";A2:B16;2;ЛОЖЬ)
or:
=VLOOKUP("Gift basket",A2:B16,2,FALSE)
=ВПР("Gift basket";A2:B16;2;ЛОЖЬ)
Another example
The next example will be a little more difficult, are you ready? Imagine that the table has a third column that stores the category of each product. This time, instead of price, we’ll define a category.
To define a category, we need to change the second and third arguments in our formula. First, change the range to A2: C16to include the third column. Next, change the column number to 3, since the categories are contained in the third column.
=VLOOKUP("Gift basket",A2:C16,3,FALSE)
=ВПР("Gift basket";A2:C16;3;ЛОЖЬ)
When you press Enter, you will see that the product Gift basket is in the category Gifts.
If you want to practice, check if you can find product data:
- Price coffee mug
- Category landscape painting
- Price serving bowl
- Category scarf
Now you know the basics of working with VLOOKUP function in Excel. Advanced users use VPR in a variety of ways, but, in fact, a lot can be done with the techniques that we have described. For example, if you have a contact list, you can find a person’s phone number by their name. If your contact list has a column with an email address or company name, you can search for that data as well by simply changing the second and third arguments, as we did in the previous example. The possibilities of Excel are endless!