Excel office suite function VLOOKUP allows you to find searched data in the specified table and in the specified column and return it as a result. This feature can be very useful when data comparison from different tables or when information is brought together into a single array for further analysis.
VLOOKUP ( VLOOKUP in English version) stands for, as a vertical view. The function is one of the most sought after in Excel. It allows, for example, to easily find and match the telephone data of a person or organizations from a lookup table by his name. You can compare the price of goods by their names. These and many other possibilities will be provided for you by the VLOOKUP function. It is quite simple to use it.
Function use
Consider the structure of the VLOOKUP, what arguments it uses. Like any other function in Excel, it starts with equal sign(=). Further name functions and arguments enclosed in parentheses.
VLOOKUP contains 4 arguments.
The function searches for the desired value in far left column and returns the value in same line from the specified column. 
Second - specifies exactly the table (or range of cells) in which this search should be performed.
Must include a column for the answer, it is located to the right of the column with the original value. 
Last Argument– interval scan, there can be 2 values: 0 – FALSE, 1 — TRUE. responsible for exact search(coincidence when viewed from top to bottom). If nothing is found, then an N/A (no data) error is returned. 1
– approximate.
For a better understanding of how the function works, consider an example of using VLOOKUP.
Examples of using
The first simple example - there are 2 tables. IN one indicate Goods and their identifiers (ID). In second, using the filter by ID, we want to get Name of product.
After the equal sign enter VLOOKUP, then Enter And fx to enter arguments. 
Arguments can also be entered on the appropriate line by listing them separated by semicolons. 
The values we are looking for will be the ones we will use to look for matches. In this case, this cell E1.
For second we single out the argument table range.
Definitely necessary fix(specify dollar signs or the F4 key for the entire range) and do absolute links so that the range does not “creep”, since in this case the table name is not specified. 
- what needs to be returned. This example wants to return 2 columns ( Product). For exact search 4 argument is . 
After entering all the values, press the button OK.
Now, when changing the ID number in the filter, it will be name change goods. 
Now let's see another example.
Now you need get a batch for each denominations goods by criterion Quantity.
For example, for shallow lot quantity must be from 100 to 200, middle – 200-300 etc.
The desired value in this case will be number, choose the table range Criteria – The consignment(fix F4). Column number 2, interval view in this case should be 1 (will allow you to get the nearest smaller value to the desired value). 
As you can see in the resulting table, for the quantity, for example, 110, the batch was issued by Small (the closest less than 100), etc. The sorting criteria must be from largest to smallest otherwise the VLOOKUP will not work. 
VLOOKUP Excel is a function in the corresponding program, which is distinguished by beauty and simplicity. It has many different applications, it is used in completely different areas: from education to retail. The basic concept of the function is to look for matches in one or more tables. So you can easily find the information of interest, spending a minimum of time.
general information
Excel - what is it? It is also called VLOOKUP in English version. This is one of the most common array and reference functions. The experts who make up the BRP ADVICE scale set the difficulty level to be equivalent to 3 or 7.
The function in question allows you to quickly find in a large table those values from rows or columns that the user needs. This program finds the first parameter on its own. The column is specified by the user. There is another function of the GPR, where a line is marked by a person. She finds the column herself.
If the user has already dealt with links and arrays, then it will be easy to understand the actions of VLOOKUP. In different spreadsheet documents, you can make a footnote to a specific cell. It is set as an example or, on the contrary, indicated as an exception. A VLOOKUP assignment usually specifies a cell in the form A1, D9, K8, and so on.
Sometimes the link is given in a different form (R1C1). In a word, a column and a line are marked, at the intersection of which the information necessary for the user is located. The program receives an exact indication of where it should look for this data.
Function parameters by example
In the Excel VLOOKUP function, the row number is listed first, followed by the column designation. Approximately it should be something like:
VLOOKUP(A1;Database;2;FALSE)
The function parameters mean the following:
- A1. This is an approximate cell reference. It can contain any value, depending on the result that the user wants to receive.
- Database. The name of the area of information to be searched. The concept is not as broad as the previous one. It can only be used on the first rows or columns.
- 2. This is the ordinal number of the column from which the program will draw information.
- FALSE. Indicates to search for an exact match. Sometimes other additional parameters of this word are specified. And the program will look for all matches and determine the nearest values.
Unfortunately, the feature couldn't work more professionally. Some data the user needs to keep in mind (row or column number). Otherwise, he will not be able to find the information he needs.
VLOOKUP Arguments
To talk about how the VLOOKUP function works in Excel, you need to familiarize yourself with its arguments. The first is the desired value. It sets the search parameters that the program will search for in the first column of the table. It cannot work with the second and subsequent, the function simply will not find this information. Inside the formula, this argument is specified in quotation marks. The exceptions are the names of functions.
Another argument is a table. It can be specified in a coordinate system. And directly in this table, its first column, the function will try to find the desired element. It is specified initially (see above).
The third argument is the column number. This is the information the user is looking for. For example, he can look at the position corresponding to the last name from the first column, his salary, grades, and so on. It all depends on the field of activity of the user.
And the last argument is interval viewing. It specifies "1" and "0" or "False" and "True". In the first case, the data will indicate that the given search is approximate. Then the program will start looking for all matches. If the second option is applied, then the function will only pay attention to exact values.
Common Mistakes
The Excel VLOOKUP function will never crash if the wrong task is marked. That is, only the user is to blame for any violations. There are three common mistakes. First, a person is often confused in the arguments "false" and "true". The first is focused on finding an exact match. If you specify "true", then the function selects approximate.
Second, an Excel VLOOKUP formula cannot be written so that the search starts from the second or subsequent column. You can find data only on the first one. Therefore, if the user enters some other formula that differs from the rules, then the program simply will not be able to recognize it.
And, thirdly, the number of the column from where the information is needed is often incorrectly indicated. In this case, you need to double-check the data.
When is the VLOOKUP function used?
This is worth talking about in detail. It's no secret that the Excel VLOOKUP function is used in absolutely different areas. Instructions for its use may seem complicated, but only at first glance. Otherwise, it would not have become so widespread.
Remember the main thing: the function looks for information vertically, that is, in columns. It is used in different situations:
- When you need to find information in a large table or find all duplicate data and matches.
- In a teaching environment. The teacher can always by the name of his students in a matter of moments to find their attendance, academic performance and other information. It is especially useful when the list of students is large, and the teacher cannot remember each of them.
- In retail. If you use the function to search for the price, composition or article of the product, then a person will be able to quickly answer customer questions.
In short, in any environment where you need to find data from a table, you can use VLOOKUP.
How to use VLOOKUP in a table?
It's easy to figure this out. In order for the Excel VLOOKUP formula to really work, you first need to make a table. Also, for the full use of the function, at least two columns are required, the maximum number of such columns is not limited.
Then, in an empty cell, you need to enter this formula, where the user sets the search parameters for matches and information. An empty niche can be located anywhere: top, bottom, right. The cells will need to expand to find the data. Since they are located in their columns, they will need at least two. If there are more search parameters, then the number of cells increases. Then, the operation of the function is checked and how correctly the formula is written. To do this, click on "view values". You can spot inconsistencies in the formula.
VLOOKUP in English Excel and in the Russian counterpart is applied in the same way. But there are a couple of tips from the pros. To make the function work better, especially after changing the data, it is recommended to enter the dollar sign between the arrays. For example, not A1, but A$1$. When primary values are driven in, then no characters and spaces between the names of rows and columns need to be set.
It is also recommended that you carefully check the table so that there are no extra punctuation marks or spaces. Their presence will not allow the program to normally search for matches, especially when it is only approximate (by the "True" parameter).
Conclusion
The Excel VLOOKUP (Vertical View) function is simple for the advanced user. But it will not be difficult for a person inexperienced in the matter to familiarize themselves with the rules for its use. After mastering them, the user will be able to quickly find information, no matter how extensive the table is. If you need to use horizontal viewing, then use the HLOOKUP function.
In the English version, this feature is called VLOOKUP - stands for Vertical View. There is also a HLOOKUP feature that is focused on horizontal viewing. Basically, the VLOOKUP function is used to pull data from one table to another, but it can also be used to compare columns in two different tables.
In order for us to more thoroughly understand all the features of working with a function, let's look at simple examples.
We have two tables. The first table stores order data for one day. It records the name of the product and the number of products sold by batch. The second table stores information on the prices of goods. The task is to calculate the total revenue for each product per day.
To solve the problem, we need to pull up the prices of goods from the "Price list" table to the "Price per kg" column of the "Orders" table. Those. substitute the prices that correspond to each product from the bottom table to the top one.
There are several possibilities in Excel to solve this example, one of which is to use the VLOOKUP function. We get to the first cell where we need to substitute the data, and start writing the function, starting as usual with the equal sign.
A hint should appear, according to which we will give it arguments. If we decipher the work of VLOOKUP from the technical language to normal, then the VLOOKUP function will look for the product we have selected from the "Orders" table in the leftmost column of the "Price List" table and, if it finds this product, display the value of its price, which is located on the same line as the item found. Briefly, it looks like the screenshot below.
The VLOOKUP function has 4 arguments. The first argument that we will substitute into the formula is the same desired value, the price for which must be found in the second table "Price List". After writing the name of the function and adding an opening bracket, click on the desired cell and add it as an argument. In my case, this is cell "E4" with the value "Apples". Next, we set the separator - a semicolon.
The second argument is the table in which we are looking for a value and from which we pull the price. Here you just need to select the "Price List" table and it's better that you don't catch extra cells (header, empty cells and just extra information). Don't forget to put a semicolon at the end.
Since in our example the table in which the search is performed is on the same sheet as the first one, when dragging the formula to the underlying cells, the specified table will automatically slide down. To prevent this from happening, you must select the range of the table inside the formula with the left mouse button and press the F4 key on the keyboard. A dollar sign will be added to the cell addresses and the reference to the table will turn from relative to absolute. Below we will analyze the cases when it will not be necessary to create absolute cell references.
The third argument is the number of the column in the "Price List" table from which we want to pull the value we need. In our example, everything is simple. In the first column, we are looking for a value, and from the second column we pull up the price. We put the number 2, and after the semicolon.
The last parameter is called "Interval View" - this is a boolean argument and can only take 2 values: 0 or 1 (on/off). With this value, we determine, exactly, the number 0, or approximately, the number 1, we are looking for the name in the price list. In the case of text names, it is better to use the exact search (number 0), because the approximate one works more or less accurately in the case of cells with numbers. Enter the interval scan as 0 and close the bracket. After that, press the Enter key for the formula to work. As a result, we will get a cell with a price pulled from the price list table.
In the column "Total cost" we write a simple formula for multiplying the weight of the batch by the price per kg.
Select both cells and, holding down the left mouse button on the lower right corner of the selection, drag the formulas down to all the remaining cells.
As a result, we get the price and the final cost of each batch. With further use of the obtained data, do not forget that they are the result of the formula, and not ready-made values. In order to be able to work with them in the future, you need to copy the cells with the resulting data and paste them back into their place through a special paste as normal values.
We have considered with you one of the cases when there is a need to pull up data from a table that is located on the same sheet. Of course, you can use tables that are on different sheets and even when they are open in different documents. In this case, the second function argument will look a little different. In the case of another sheet or document, it is absolutely not necessary to set absolute links, and it is better to add the third and fourth arguments in the input field of the function, without returning back to the sheet where you started typing it.
Another useful use of VLOOKUP is to compare two tables to find matches or differences. For example, if you remove several rows in one of the tables, then there will no longer be a complete match in the data and in some cells the inscription #N/A will appear. This inscription indicates that the values from the table with orders were not found in the price list table, which accordingly leads to an error.
Now let's consider a case where there is a need to use an interval scan not exact, with the number 0, but approximate, with the number 1. This method is rarely used, but nevertheless there are tasks in which you may need it.
Let's go back to our table with orders and try to determine the size of the lot by its weight. We will have a table with orders and a table with batch parameters.
Additionally, we have a criterion "from and to", which indicates the size of the party. In the example, we will build on its weight. For example, if Excel shows a batch weight of 15 kg, it will understand that this is a small batch.
Let's understand how it works. We start writing the VLOOKUP function, but at the end of the formula we put not 0, but 1. In the argument where you need to specify a table with cells, we will not specify a column with the "From and to" parameter, since Excel will not understand such a record. Instead, we introduce a criterion field, which will determine the beginning and end of the weight range. At the end, press the Enter key and drag to all the cells.
There are some features of the work that need to be explained in order to understand what happened here. We see that at a certain weight, the function pulled out a certain batch from the second table. To understand the logic more precisely, let's look at the weight, for example, 15 kg. In the table with lot sizes, there is no such criterion. But when we set the interval lookup to 1, the function will pull out the value that is the nearest smaller value. With a batch weight of 15 kg, VLOOKUP will pull out a value with a criterion of 10 from the table. If the batch weight is 47 kg, then the function will pull out a value with a criterion of 30, which will be the closest and smaller.
When working with a function in this mode, there is a very important point that you must keep in mind. Sorting criteria must necessarily go in ascending order - from smallest to largest. If the criteria are sorted in reverse order, the function will not work.
Using the function VLOOKUP when working in Excel, you can extract the required information from spreadsheets. For these purposes, Excel offers several functions, but VLOOKUP among them the most common. In this lesson, we will learn about the function VLOOKUP, and also consider its capabilities using a simple example.
Function VLOOKUP(vertical scan) looks for the value in the leftmost column of the range being explored, and then returns the result from the cell that is at the intersection of the found row and the given column.
Example 1
For example, the figure below shows a list of 10 surnames, each surname has its own number. It is required to extract the last name by the given number.
Using the function VLOOKUP making it easy enough:
It can be seen from the formula that the first argument of the function VLOOKUP is cell C1, where we indicate the desired number. The second range is A1:B10, which shows where to look. And the last argument is the number of the column from which you want to return the result. In our example, this is the second column. Clicking Enter, we get the desired result:
Example 2
Let's consider one more example. The picture below shows the same 10 names as before, only the numbers come with gaps.
If you try to find the last name for a non-existent number (for example, 007), then the formula, instead of giving an error, will safely return the result to us. How can this be?
The point is that the function VLOOKUP also has a fourth argument, which allows you to set the so-called interval scan. It can have two values: TRUE and FALSE. Moreover, if the argument is omitted, then this is equivalent to true.
In the case when the fourth argument is TRUE, the function first looks for an exact match, and if there is none, then the nearest one, which is less than the given one. That is why the function VLOOKUP returned the name "Panchenko". If we had given "008", then the formula would also have returned "Panchenko".
When the fourth function argument VLOOKUP is the boolean value FALSE, the function looks for an exact match. For example, in the figure below, the formula will return an error because no exact match was found.
If the fourth function argument VLOOKUP is TRUE or omitted, then the leftmost column must be sorted in ascending order. If this is not done, the function VLOOKUP may return an incorrect result.
For those who like to create not vertical, but horizontal tables, there is an analogue in Excel VLOOKUP, but for horizontal search.
Horizontal VLOOKUP in Excel
There is a function in Microsoft Excel GPR(horizontal view), which is very similar to VLOOKUP, the only difference is that the range is viewed not vertically, but horizontally. GPR searches for the given value in the top row of the examined range and returns the result from the cell that is at the intersection of the found column and the given row.
If we represent the above example in a horizontal form, then the formula will look like this:
As you can see, everything is quite simple!
This concludes our lesson. Today we got acquainted with probably the most popular Microsoft Excel tool - VLOOKUP function and analyzed its capabilities with a few simple examples. I hope that this lesson was useful for you. All the best to you and success in learning Excel.
