The VLOOKUP function. Using the VLOOKUP function. Excel - VPR
The Excel application is popular thanks toits accessibility and simplicity, since it does not require special knowledge and skills. The table view of the provision of information is understandable to any user, and a wide range of tools including the "Master of Functions" allows any manipulations and calculations with the provided data.
How Excel works
When working with the VLR formula, it should be taken into account thatit searches for the search value only by columns, not by rows. To use the function, the minimum number of columns is required - two, the maximum is absent.
The VLOOKUP function searches for a given criterion,which can have any format (text, numeric, monetary, by date and time, etc.) in the table. In the case of finding a record, it outputs (substitutes) the value entered in the same row, but from the required column of the table, that is, corresponding to the specified criterion. If the desired value is not found, then the error # N / A (in the English version # N / A) is output.
Need to use
The VLOOK function comes to the aid of the operator, whenit is required to quickly find and apply in a further calculation, analysis or forecast a certain value from a table of large sizes. The main thing when using this formula is to make sure that the specified search area has been correctly selected. It should include all records, that is, from the first to the last.
The most frequent application of VLF (functionExcel) is the comparison or addition of data in two tables, using a certain criterion. And search ranges can be large and accommodate thousands of fields, placed on different sheets or books.
The VLOOK function is shown, how to use it, howperform calculations, as an example in the figure above. Here the table of the sizes of retail sales is considered depending on the region and the manager. The search criterion is a specific manager (his name and surname), and the sought-for value is the amount of his sales.
As a result of the VLOOKUP function, a new table is created in which a specific wanted manager quickly compares his sales amount.
The algorithm for filling the formula
The VLOOK formula is located in the "Function Wizard" tab and the "References and arrays" section. The function dialog has the following appearance:
Arguments in the formula are entered in the order of the queue:
- The search value is what the function should find, and the options are the cell values, its address, the name given to it by the operator. In our case, this is the surname and name of the manager.
- Table - the range of rows and columns in which the criterion is searched.
- The column number is its ordinal number, in which the sales amount is located, that is, the result of the formula work.
- Interval view. It holds the value either FALSE or TRUE. And FALSE returns only an exact match, TRUE - allows you to search for an approximate value.
Example of using the function
The VLOOKUP function may have an example of usethe following: when conducting business of a trading enterprise in the Excel tables in column A, the name of the product is recorded, and in column B - the corresponding price. To write a sentence in column C, you need to find the cost for a certain product, which you want to output in column D.
| A | AT | FROM | D |
| product 1 | 90 | product 3 | 60 |
| product 2 | 120 | product 1 | 90 |
| product 3 | 60 | product 4 | 100 |
| product 4 | 100 | product 2 | 120 |
The formula written in D will look like this: = VLP (C1, A1: B5; 2; 0), that is, = VLOOK (target value, data range of the table, column ordinal, 0). As the fourth argument, instead of 0, you can use FALSE.
To fill out the table of sentences, the resulting formula must be copied to the entire column D.
To fix the working data area, you canwith the help of absolute references. To do this, the $ signs are preceded manually before the alphabetic and numerical values of the addresses of the extreme left and right cells of the table. In our case, the formula takes the form: = ВПР (С1; $ А $ 1: $ В $ 5; 2; 0).
Errors in use
The VLOOKUP function does not work, and then a message appears in the output column of the error result (# N / A or # N / A). This happens in such cases:
- The formula is introduced, and the column of the required criteria is not filled (in this case, column C).
- Column C has a value that is missingin column A (in the range of data search). To check the availability of the desired value, select the column of criteria and on the tab menu "Edit" - "Find" insert this entry, start the search. If the program does not find it, then it is missing.
- The formats of the cells of columns A and C (the required criteria)different, for example, one - text, and the other - numeric. You can change the cell format if you go to cell editing (F2). Such problems usually arise when importing data from other applications. In order to avoid such errors, the following functions can be built into the VLOOK formula: KEY or TEXT. The execution of these algorithms automatically converts the format of the cells.
- There are unprintable characters or spaces in the function code. Then you should carefully check the formula for the presence of input errors.
- An approximate search is specified, that is, the fourththe argument of the VLOOK function has a value of 1 or TRUE, and the table is not sorted by an ascending value. In this case, the column of sought criteria must be sorted in ascending order.
Moreover, when organizing a new summary table, the specified search criteria can be in any order and sequence and need not be contained in a complete list (partial selection).
Features of use as an interval view 1 or TRUE
Error under number 5 is quite common and is clearly illustrated in the figure below.
In this example, a list of names according to the numberingsorted not in ascending order, but in descending order. And as an interval view, the TRUE criterion (1) was used, which immediately interrupts the search when a value greater than the sought value is found, so an error is produced.
When applying 1 or TRUE in the fourth argumentIt is necessary to make sure that the column with the required criteria is sorted in ascending order. With the use of 0 or FID, this need disappears, but there is also no possibility of interval scanning.
Just keep in mind that it is especially important to sort the interval tables. Otherwise, the VLOOKUP function will output incorrect data to the cells.
Other nuances when working with the VLOOKUP function
For the convenience of working with such a formula, you can title the range of the table in which the search is performed (the second argument), as shown in the figure.
In this case, the sales table area is entitled. For this purpose, a table is selected, with the exception of column headers, and the name field (on the left under the tab bar) is named.
Another option - title - implies the selection of a range of data, then go to the menu "Insert" - "Name" - "Assign".
In order to use the data placedon another sheet of the workbook, using the VLOOKUP function, you need to specify the location of the data range in the second argument of the formula. For example, = VPR (Al; List2! $ A $ 1: $ B $ 5; 2; 0), where Sheet2! - is a link to the required sheet of the book, and $ A $ 1: $ In $ 5 - the address of the range of data retrieval.
An example of organization of educational process with VPR
It is quite convenient in Excel to use the VPR function notonly to firms engaged in trade, but also to educational institutions to optimize the process of comparing pupils (students) with their assessments. Examples of these tasks are shown in the figures below.
There are two tables with student lists. One with their estimates, the second indicates age. It is necessary to compare both tables so that, along with the age of the students, their grades are deduced, that is, to enter an additional column in the second list.
The VLOOK function perfectly copes with the solution of thistasks. In the column G under the heading "Estimates" the corresponding formula is written: = VPR (E4, B3: C13, 2, 0). It must be copied to the entire column of the table.
As a result, the VLR function will produce estimates received by certain students.
An example of organizing a search engine with a VLOOK
Another example of using the VLOOK function isthe organization of the search engine, when in the database according to the specified criteria it is necessary to find the value corresponding to it. So, the figure shows a list with the nicknames of animals and their belonging to a particular species.
With the help of the VLOOK, a new table is created, in whichit is easy to find the animal's look by the name of the animal. These search engines are relevant when working with large lists. In order not to manually review all records, you can quickly use the search and get the desired result.
</ p>
