The VLOOKUP function. Using the VLOOKUP function. Excel - VPR

The Excel application is popular due to its availability and simplicity, since it does not require special knowledge and skills. The tabular view of the provision of information is understandable to any user, and a wide range of tools including the "Function Wizard" allows you to perform any manipulations and calculations with the data provided.

One of the well-known Excel formulas is vertical viewing. The use of the VLOOK function at first glance seems rather complicated, but it is only at first.

How Excel works

When working with the VLR formula, it should be taken into account that it searches for the sought value exclusively by columns, not by rows. To use the function, you need a minimum number of columns - two, the maximum is missing.

The VLOOK function searches for a given criterion, which can have any format (text, numeric, monetary, 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 VLOOKUP function comes to the aid of the operator when it is required to quickly find and apply a certain value from a larger table in further calculations, analysis or forecasting. 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 common case of using VLOOK (Excel function) is to compare or add data in two tables using a certain criterion. And the search ranges can be large and accommodate thousands of fields, placed on different sheets or books.

The VLF function is shown, how to use it, how to perform calculations, as an example in the figure above. Here is a table of the sizes of retail sales, 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 the specific manager in question 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 the following example: when dealing with a business in an Excel table, column A indicates the name of the product, and column B shows 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.

The formula written in D will look like this: = VPR (C1, A1: B5; 2; 0), that is, VLOOK (search value, data range of the table, column ordinal, 0). As the fourth argument, instead of 0, you can use FALSE.

To fill the table of the sentence, the resulting formula must be copied to the entire column D.

To fix the area of the working range of data, you can use 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:

1. The formula is introduced, and the column of the required criteria is not filled (in this case, column C).
2. Column C contains a value that is missing in 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 "Edit" - "Find" insert this entry, start the search. If the program does not find it, then it is missing.
3. The formats of the cells of columns A and C (the required criteria) are different, for example, one has a text one, and the other has a numeric one. You can change the cell format if you go to cell editing (F2). Such problems usually arise when you import 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.
4. There are non-printable characters or spaces in the function code. Then you should carefully check the formula for the presence of input errors.
5. An approximate search is specified, that is, the fourth 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 given required 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

The error under number 5 is quite common and is clearly illustrated in the figure below.

In this example, the list of names according to the numbering is sorted not in ascending order, but in a 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 argument, you need to make sure that the column with the sought criteria is sorted in ascending order. If you use 0 or FAL, 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. To do this, the table is selected, with the exception of the column headings, and the name in 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 data placed on 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 (A1; List2! $ A $ 1: $ B $ 5; 2; 0), where Sheet2! - is a link to the required sheet of the book, and $ A $ 1: $ B $ 5 - the address of the range of data retrieval.

An example of organizing an educational process with a VLF

It is quite convenient in Excel to use the VPR function not only for firms engaged in trade, but also for 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 this problem. 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 issue estimates received by certain students.

An example of organizing a search engine with a VLOOK

Another example of the use of the VLOOK function is the organization of the search engine, when in the database according to the specified criterion it is necessary to find the value corresponding to it. Thus, the figure shows a list with nicknames of animals and their belonging to a particular species.

With the help of the VLR, a new table is created, in which it is easy to find the animal's name by the name of the animal. These search engines are relevant when working with large lists. In order not to manually revise all records, you can quickly use the search and get the desired result.