The Excel application is popular for its accessibility and simplicity, because it does not require special knowledge and skills. The tabular form of providing information is understandable to any user, and a wide range of tools, including the "Master Function", allows you to carry out any manipulations and calculations with the data provided.
One of the well-known Excel formulas is vertical viewing. Using the VLOOKUP function at first glance seems rather complicated, but this is only at first.
How Excel VLOOKUP Works
When working with the VLOOKUP formula, it should be borne in mind that it searches for the desired value exclusively in columns, and not in 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 be of any format (text, numerical, monetary, by date and time, etc.) in the table. If a record is found, it gives (substitutes) the value entered in the same row, but from the desired 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 issued.
Need to use
The VLOOKUP function comes to the aid of the operator when it is necessary to quickly find and apply a specific value from a large table in further calculations, analysis or forecasting. The main thing when using this formula is to ensure that the specified search area is correctly selected. It should include all entries, that is, from the first to the last.
The most common use of VLOOKUP (Excel function) is to compare or add data in two tables using a specific criterion. Moreover, the search ranges can be large and accommodate thousands of fields, placed on different sheets or books.
The VLOOKUP function is shown, how to use it, how to carry out calculations, as an example in the figure above. Here is a table of retail sales sizes by region and manager. The search manager is the specific manager (his name and surname), and the desired value is the sum of his sales.
As a result of the VLOOKUP function, a new table is formed in which the specific sales manager is quickly compared with its sales amount.
Formula Algorithm
The VLOOKUP formula is located in the "Function Wizard" tab and the "References and Arrays" section. The function dialog box looks like this:
Arguments in the formula are entered in order of priority:
- The desired value is what the function should find, and the variants of which are the values of the cell, 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 sum of sales is located, that is, the result of the formula.
- Interval viewing. It contains the meaning of either FALSE or TRUE. Moreover, FALSE returns only an exact match, TRUE - allows searching for an approximate value.
Function example
The VLOOKUP function can have an example of use as follows: when conducting business of a commercial enterprise in Excel tables, column A contains the name of the product, and column B shows the corresponding price. To draw up a proposal in column C, you need to find the cost of a particular product, which you want to display in column D.
A good example of organizing a table| A | IN | 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: = VLOOKUP (C1; A1: B5; 2; 0), i.e. = VLOOKUP (desired value; table data range; column serial number; 0). Instead of 0, you can use FALSE as the fourth argument.
To fill out the proposal table, the resulting formula must be copied to the entire column D.
You can pin an area of the working data range using absolute links. To do this, manually put $ signs in front of the alphanumeric values of the addresses of the leftmost and rightmost cells of the table. In our case, the formula takes the form: = VLOOKUP (C1; $ $ 1: $ $ 5; 2; 0).
Usage errors
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 has been entered, and the column for the desired criteria has not been filled (in this case, column C).
- Column C contains a value that is not in column A (in the data search range). To check the presence of the desired value, select the criteria column and insert this record in the tab menu "Edit" - "Find", start the search. If the program does not find it, then it is missing.
- The cell formats of columns A and C (of the desired criteria) are different, for example, one has text, and the other has numerical. You can change the cell format if you go to cell editing (F2). Such problems typically occur when importing data from other applications. To avoid such errors in the VLOOKUP formula, it is possible to embed the following functions: VALUE or TEXT. Execution of these algorithms automatically converts the format of the cells.
- The function code contains unprintable characters or spaces. Then you should carefully check the formula for input errors.
- An approximate search is specified, that is, the fourth argument of the VLOOKUP function has a value of 1 or TRUE, and the table is not sorted by ascending value. In this case, the column of the desired criteria is required to be sorted in ascending order.
Moreover, when organizing a new pivot table, the specified desired criteria can be in any order and sequence and it is not necessary to fit in a complete list (partial selection).
Features of use as interval viewing 1 or TRUE
Error under No. 5 is quite common and is clearly shown in the figure below.
In this example, the list of names according to the numbering is sorted not by ascending order, but by descending value. Moreover, as an interval scan, the TRUE criterion (1) was used, which immediately interrupts the search when a value greater than the desired value is detected, therefore an error is generated.
When applying 1 or TRUE in the fourth argument, you need to ensure that the column with the desired criteria is sorted in ascending order. When using 0 or FALSE, this need disappears, but then there is also no possibility of interval viewing.
Just keep in mind that sorting interval tables is especially important. 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 headline the range of the table in which the search is performed (second argument), as shown in the figure.
In this case, the area of the sales table is entitled. To do this, a table is selected, with the exception of column headings, and in the name field (to the left under the tab bar) is assigned a name.
Another option - title - involves highlighting the data range, then going to the "Insert" menu - "Name" - "Assign".
In order to use the data placed on another sheet of the workbook using the VLOOKUP function, it is necessary to specify the location of the data range in the second argument of the formula. For example, = VLOOKUP (A1; Sheet2! $ A $ 1: $ B $ 5; 2; 0), where Sheet2! - is a link to the required sheet of the book, and $ A $ 1: $ B $ 5 is the address of the data search range.
An example of the organization of the educational process with CDF
It is quite convenient in Excel to use the VPR function not only for firms involved in trade, but also for educational institutions to optimize the process of comparing pupils (students) with their grades. Examples of these tasks are shown in the figures below.
There are two tables with student lists. One with their ratings, the second indicates age. It is necessary to compare both tables so that, along with the age of the students, their grades are also displayed, that is, enter an additional column in the second list.
The VLOOKUP function copes with the solution to this problem. In the column G under the heading "Ratings" the corresponding formula is written: = VLOOKUP (E4, B3: C13, 2, 0). It needs to be copied to the entire column of the table.
As a result of the implementation, the VLOOKUP function will give out marks received by certain students.
Example of organizing a search engine with VLOOKUP
Another example of the application of the VLOOKUP function is the organization of a search system when, according to a given criterion, a value corresponding to it should be found in the database. So, the figure shows a list with the nicknames of animals and their belonging to a certain species.
With the help of VLOOKUP, a new table is created in which it is easy to find its appearance by the name of the animal. Similar search engines are relevant when working with large lists. In order not to manually review all entries, you can quickly use the search and get the desired result.