The Vlookup Excel function in Russian is written as VLOOKUP. With its help, a specified value is searched for among a certain set of data of values given in one column.
The need for CDF
The need to use the function arises when comparing data and searching for the latter located in another table, if you need to add information from one table to another, selecting them according to the necessary criterion.
How to use Vlookup in Excel will be shown in this article.
Function Syntax
Vlookup Excel function = VLOOKUP in Russian. The syntax for this function is as follows:
- VLOOKUP (value_assigned_to_search, table_of_search_of_search_ performed, column_number, [true_false]).
Using the VLOOKUP function, values are searched in the leftmost column of the table, and the values of the cell located in the same row in this column are returned.
Using this function, the values that are displayed in the source table are selected, and by the given column number determines the value that must be inserted into the second table. The search is performed according to the value specified for the search, and then it is transferred from the column that was specified for the transfer (column_number).
VLOOKUP EXAMPLE
How to use Vlookup in Excel based on the above syntax?
Let's look at an example when we have the full name, dates of recruitment and departments in which these employees function in our table. Suppose that we need to substitute the adoption date values from one table to another. In the event that the values are few, this can be done manually.
But if there are many surnames, they are located in different tables in a chaotic order, the number of surnames is not the same, and besides, they are located on different sheets of the spreadsheet, then the task is much more complicated.
Or another example of Vlookup in Excel - there is a price list in which the prices of specific goods are indicated, and there is a table in which to drag these prices, drive in the number of goods sold in order to automatically calculate the daily turnover.
The Vlookup function (VLOOKUP) searches for the value that is set for the search in the left column of the specified table, while moving from top to bottom.
Suppose that in this example of using Vlookup in Excel, products are searched for under the common name "Cat Food".
By default, the column of the spreadsheet with price lists “Product Name” is sorted in ascending order. The format of this column in the two tables should be the same.
We click on the cell in the table for calculating the turnover, in which the price from the price list will be inserted.
On the toolbar, click on the fx icon and select the VLOOKUP function, which can be found by searching, as well as browsing the "Links and Arrays" category.
After confirming the choice of function, a dialog box opens in which you need to set the values:
- initial value - indicate the cell into which the price of the desired product will be substituted;
- table - indicate the range of the Price List table in full;
- column number - indicate the column number in the Price List table;
- interval viewing - indicate “FALSE” (this is a little incomprehensible to the average user, since “FALSE” shows the exact value, and “TRUE” indicates the approximate value).
We confirm the entered values, as a result we get the calculation. Thus, in this example, we examined some points of using Vlookup in Excel.
Improving input
Suppose we have not two tables, but many tables. In this situation, in order to find the table that will need to be allocated, you need to spend a certain amount of time.
To facilitate the work, you can immediately select them when entering certain tables, and using the context menu give the name of the range, which is then used in the formula instead of specifying the range of the table.
Thus, we examined the basic conditions for using Vlookup in Excel.
Error # N / A (# N / A) in VLOOKUP (Vlookup)
If this function does not find the desired value, then # N / A (or # N / A if the version is English) is entered in the cell into which it should have been written.
To prevent this inscription from appearing, that is, leaving the cell empty if the required value is not found, in Excel before 2007, enter = IF (UND (VLOOKUP (; Sheet1!; Column number; value “True” = 1 or “False” = 0 )); ""; VLOOKUP (copy the same as in the previous bracket)))
From the 2007 version this formula can be simplified = IF ERROR (VLOOKUP (specify the same as in the previous formula); "")
VLOOKUP with several conditions
If it is necessary to carry out a search according to several requirements, it is necessary to create two conditions for carrying out a search operation, add a column in which columns with data will be combined in the future, and a search will be performed on them.
This will allow us to get one column necessary for us, in which we can use the formula in the same way as described above.
For example, we have suppliers who supply various materials. Suppliers appear in the first column, and products in the second. If you search only by suppliers, we may get to the wrong product; even if you rearrange the columns, the user will be taken to that product, but from a different supplier. Therefore, the two columns are combined using the &: = B2 & C2 sign. As a result, a combined column will be placed in column A (if this formula was entered into A2 and then pulled), by which the search will be carried out similarly to the previous one.
How does Vlookup work in Excel when pasting values without binding to a table?
When any formulas in a book are set, when you open it next time, the default recalculation of these formulas occurs. Not an exception is the use of the VLOOKUP function. Suppose that our data in the source table changes, then the data automatically changes in the table in which the VLOOKUP function was used, but we do not need this for any reason. In this case, after applying the function, you need to copy the values and paste them using a special paste, selecting "Paste" - "Values" in the "Paste Special" menu. As a result, the table is inserted only with values without formulas.
Writing Your Vlookup
Using VBA, Vlookup in Excel, you can create yourself, or rather, make a macro that will be somewhat similar to VLOOKUP, but will be designed to perform specific tasks of a specific user. So, for example, when comparing, it is necessary to perform a double check, since some comparisons may turn out to be more reliable than others. If the VLOOKUP searches only for the left column, then it is possible to create a macro with the help of which the search in the refinement column will be performed upon successful completion of the search in the base column.
It is allowed to use VBA to replace the VLOOKUP function. For example, there is a table in which a number of people and their salaries are listed, which developed in stages. We want to pull out the salary of one of the workers. To do this, we write a macro:
Sub VLookupFunctionDemo ()
Dim d1 as double
d1 = Application.WorksheetFunction.Vlookup ("worker_name_of_first_column", Range ("table_range"), column_number)
MsgBox Format (d1, "Currency"),, "VBA VLookup Function"
End sub
Finally
This article examined the question: "How to use Vlookup in Excel?" Using this function can greatly facilitate the lives of those users who are looking for data on different tables, are engaged in comparing various information. The use of VLOOKUP is useful for those users who add information from one table to another with a search according to a given criterion. In some cases, the functions may not be enough, then users who understand the VBA language can create macros that extend the functionality of Vlookup.