What is a demo version? VLOOKUP function in Excel - Example of a VLOOKUP function with a range name. It is necessary to fill in the values ​​for the VLOOKUP function

Microsoft Excel - great tool for working with data. The possibilities are enormous, but the potential has, in fact, been little studied, since the rules for writing commands cause difficulties even for professionals. But if you work hard, it turns out that you can do incredible things here. The Excel VLOOKUP function is one of the tools for data processing, and we will talk about it now.

What is the VLOOKUP function in Excel - scope

When processing several tables, often located in different books, there is a need to transfer data from one to another, while ensuring that the values ​​do not lose their meaning and the process is automated. The mechanism in Excel should work simply and quickly.

For example, we have an enterprise. Ivan Ivanovich works there. On one sheet the value of his salary is stored, on the other the amount of money that the accounting department withholds from the salary for a fine. You need to combine all the values ​​into one document. Another example, there are two tables: prices and warehouse. One indicates the cost of handkerchiefs, the other their number. It is necessary to consolidate the quantity and price in one place.

In cases where there are only two or three employees of the enterprise, or up to a dozen goods, you can do everything manually. With due care, a person will work without errors. But if there are, for example, a thousand values ​​to process, automation of the work is required. For this purpose, Excel has VLOOKUP.

Examples for clarity: in tables 1,2 - the initial data, table 3 - what should happen.

Initial data table 1

Combined data table 3

VLOOKUP function in Excel - how to use

In order for Table 1 to reach its final form, we enter a column heading in it, for example “Fine”. In fact, this is not necessary; you can write any text or leave it blank. The function will also work by clicking the mouse in the field where the value found in another table should appear.

Now we need to call the function. This can be done in different ways:

It is necessary to fill in the values ​​for the VLOOKUP function

The result is obvious - in table 3 (see above).

VLOOKUP – instructions for working with two conditions

In situations where it is necessary to set search criteria not by one condition, but by several or two columns at once, the VLOOKUP function can also be used for work. To do this, the initial data must be modified.

Example, you need to insert the price from table 5 into table 4.

Phone characteristics table 4

The example chosen is on phones, but it is clear that the data can be absolutely anything. As you can see from the tables, the phone brands do not differ, but the RAM and Camera differ. To create summary data, we need to select phones by brand and RAM. To use the VLOOKUP function for several conditions, you need to combine columns with conditions.

Add the leftmost column. For example, we call it “Unification”. In the first cell of values, we have B 2, we write the construction “= B 2& C 2”. We reproduce using the mouse. It turns out as in table 6.

Phone characteristics table 6

We process Table 5 in exactly the same way. Then we use the VLOOKUP function to search by one condition. The condition is data from the joined columns. Don't forget that the column number where the data comes from in the VLOOKUP function will change. After applying the function, a selection will be obtained based on two conditions. You can merge not adjacent columns, but columns with the phone brand and camera.

Watch the video tutorial on how to use the VLOOKUP function in Excel for dummies:

The VLOOKUP function in Excel is not difficult to use, and the possibilities for data processing are enormous.

When you need to find data by row in a table or range, use the VLOOKUP function, one of the link and search functions. For example, you can find the price car parts by her number.

Advice: Check out these videos to get Additional information about the VLOOKUP function!

The most simple function VPR means the following:

VLOOKUP(search value; range to search for value; column number in the range with the returned value; exact or approximate match - specified as 0/FALSE or 1/TRUE).

Advice: Secret function A VLOOKUP is arranging the data so that the lookup value (fruit) appears to the left of the return value (value) you want to find.

Use the VLOOKUP function to look up a value in a table.

Syntax

VLOOKUP(lookup_value, table, column_number, [interval_lookup])

For example:

VLOOKUP(105,A2:C7,2,TRUE)

VLOOKUP("Ivanov";B2:E7;2;FALSE)

Beginning of work

To build the VLOOKUP function syntax you will need the following information:

The value you need to find is the value you are looking for.

The range in which the search value lies. Remember that for proper operation In the VLOOKUP function, the value you are looking for must always be in the first column of the range. For example, if the value you are looking for is in cell C2, the range must start at C.

The column number in the range that contains the return value. For example, if you specify B2:D11 as your range, you would consider B to be the first column, C to be the second, and so on.

Optionally, you can specify the word TRUE if you need an approximate match, or the word FALSE if you need exact match return value. If you do not specify anything, the default is always TRUE, which is an approximate match.

Now combine all the above arguments as follows:

VLOOKUP(search value; range with search value; column number in range with return value; optionally TRUE for an approximate match or FALSE for an exact match).

Examples

Here are some examples of the VLOOKUP function:

Example 1

Example 2

Example 3

Example 4

Example 5

Common problems

This article is dedicated to VLOOKUP functions. It will consider step-by-step instruction VLOOKUP functions, entitled "". In this article we will take a detailed look at the description, syntax and examples of the VLOOKUP function in Excel. We’ll also look at how to use it and look at the main mistakes and why. VLOOKUP function does not work.

Syntax and description of the VLOOKUP function in Excel

So, since the second title of this article is “ VLOOKUP function in Excel for dummies", let's start by finding out what the VLOOKUP function is and what it does? VLOOKUP function in English VLOOKUP, looks up the specified value and returns the corresponding value from another column.

How the VLOOKUP function works? The VLOOKUP function in Excel searches your lists of data based on unique identifier and provides you with a piece of information associated with that unique identifier.

The letter "V" in VPR stands for "vertical". It is used to differentiate VLOOKUP and GLOOKUP functions, which looks up the value in top line array (“H” stands for “horizontal”).

The VLOOKUP function is available in all Excel versions 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003.

Syntax VLOOKUP functions as follows:

VLOOKUP(lookup_value, table, column_number, [interval_lookup])

As you can see, VLOOKUP function has 4 parameters or arguments. The first three parameters are required, the last is optional.

1. lookup_value is the search value.

This can be either a value (number, date or text) or cell reference(a reference to the cell containing the lookup value), or the value returned by some other Excel function. For example:

• Search for a number: =VLOOKUP(40; A2:B15; 2) - the formula will search for the number 40.
• Text search: =VLOOKUP(“apples”, A2:B15, 2) - the formula will search for the text “apples”. Please note that you always include text values in "double quotes".
• Searching for a value from another cell: =VLOOKUP(C2, A2:B15, 2) - the formula will search for the value in cell C2.

1. a table is two or more columns of data.

remember, that VLOOKUP function always looks for the desired value in the first column of the table. Your table can contain various values ​​such as text, date, numbers or boolean values. Values case insensitive, which means that uppercase and lower case are considered identical.

So our formula =VLOOKUP(40, A2:B15, 2) will look for "40" in cells A2 to A15 because A is the first column of the table A2:B15.

1. column_number - the number of the column in the table from which the value in the corresponding row should be returned.

The leftmost column in the specified table is 1, the second column is 2, the third is 3, etc.

4. interval_view determines whether you are looking for an exact match (FALSE) or an approximate match (TRUE or omitted). This last parameter is optional, but very important.

VLOOKUP function in Excel examples

Now let's look at some use cases VLOOKUP functions for real data.

VLOOKUP function on different sheets

On practice VLOOKUP formulas rarely used to search data on a single sheet. More often than not, you will have to search and pull the relevant data from another sheet.

To use the VLOOKUP function from another Excel sheet, you must enter the worksheet name and Exclamation point in the table argument before the range of cells, for example, =VLOOKUP(40;Sheet2!A2:B15;2). The formula indicates that the search range A2:B15 is in Sheet2.

Of course, you don't have to enter the sheet name manually. Just start typing the formula, and when it comes to the table argument, switch to the lookup worksheet and select the range with your mouse.

The formula you see in the image below searches for text in cell A2 ("Product 3") in column A (1st column of search range A2:B9) on the "Prices" worksheet:

VLOOKUP(A2,Prices!$A$2:$B$8,2,FALSE)

VLOOKUP function in Excel - VLOOKUP function on different sheets

How to use a named range or table in VLOOKUP formulas

If you intend to use the same lookup range in multiple VLOOKUP formulas, you can create and enter a name for it directly in the table argument of your VLOOKUP formula.

To create a named range, simply select the cells and enter any name in the Name box to the left of the Formula panel.

VLOOKUP Function in Excel - Naming a Range

Now you can write the following VLOOKUP formula to get the price of Product 1:

VLOOKUP("Product 1";Products;2)

VLOOKUP function in Excel - Example of a VLOOKUP function with a range name

Most range names in Excel apply to the entire workbook, so you don't have to specify a worksheet name, even if your search range is on a different worksheet. Such formulas are much more understandable. Also, using named ranges can be a good alternative to cells. Because the named range doesn't change when the formula is copied to other cells, you can be sure that your search range will always remain correct.

If you've converted a range of cells to a full-featured Excel table (Insert tab --> Table), you can select the search range with your mouse, and Microsoft Excel will automatically add the column names or table name to the formula:

VLOOKUP function in Excel - Example of VLOOKUP function with table name

The full formula might look something like this:

VLOOKUP("Product 1";Table6[[Product]:[Price]];2)

or even =VLOOKUP("Product 1";Table6;2).

Like named ranges, column names are constant, and cell references will not change no matter where the VLOOKUP formula is copied.

VLOOKUP function with multiple conditions

Let's consider example of a VLOOKUP function with several conditions. We have the following input data:

VLOOKUP function in Excel - Source data table

Suppose we need to use VLOOKUP function with several conditions. For example, to search for the price of a product using two criteria: the name of the product and its type.

1. In order to use VLOOKUP function with several conditions it is necessary to insert an additional column at the beginning that will store information with the name and type of product.

So, on the “Prices” sheet, insert a column and in cell A2 enter the following formula:

Using this formula, we obtain the value of the “Product” and “Type” columns. Fill in all the cells.

Now the lookup table looks like this:

VLOOKUP Function in Excel - Adding a Helper Column

1. Now in cell C2 on the “Sales” sheet we will write the following VLOOKUP formula:

VLOOKUP(A2Prices!$A$1:$D$8,4,FALSE)

We fill in the remaining cells and as a result we get prices for each product in accordance with the type:

VLOOKUP function in Excel - Example VLOOKUP with several conditions

Now let's look at it VLOOKUP function errors.

Why doesn't the VLOOKUP function work?

In this part of the article we will look at why VLOOKUP function does not work and possible VLOOKUP function errors.

I hope that now even for dummies VLOOKUP function in Excel will be clear.

VLOOKUP is an Excel function that allows you to search a specific column using data in another column. The VLOOKUP function in Excel is also used to transfer data from one table to another. There are three conditions:

1. Tables must be located in one Excel workbook.
2. You can only search among static data (not formulas).
3. The search term must be in the first column of the data used.

VLOOKUP formula in Excel

VLOOKUP syntax in Russified Excel it looks like:

VLOOKUP (search criterion; data range; column number with result; search condition)

The arguments required to search for the final result are indicated in parentheses.

Search criteria

The address of the Excel sheet cell in which the data to be searched in the table is specified.

Data range

All addresses among which the search is carried out. The first column should be the one in which the search criterion is located.

Column number for total value

The column number from which the value will be taken if a match is found.

Search condition

A Boolean value (true/1 or false/0) that specifies whether to look for an approximate match (1) or an exact match (0).

VLOOKUP in Excel: function examples

The principle of operation of the function is simple. The first argument contains the search criteria. As soon as a match is found in the table (second argument), information is taken from the desired column (third argument) of the found row and inserted into the cell with the formula.
A simple use of VLOOKUP is to look up values ​​in an Excel table. It has meaning in large volumes of data.

Let's find the number of products actually released by the name of the month.
The result will be displayed to the right of the table. In the cell with address H3 we will enter the desired value. In the example, the name of the month will be indicated here.
In cell H4 we enter the function itself. This can be done manually, or you can use a wizard. To call, place the pointer on cell H4 and click the Fx icon next to the formula bar.


A wizard window will open Excel functions. You need to find a VLOOKUP in it. Select “Full Alphabetical List” from the drop-down list and start typing VLOOKUP. Highlight the found function and click OK.


A VLOOKUP window will appear for Excel tables.


To specify the first argument (criteria), place the cursor in the first row and click on cell H3. Her address will appear in the line. To select a range, place the cursor in the second line and start selecting with the mouse. The window will be minimized to a line. This is done so that the window does not interfere with your ability to see the entire range and does not interfere with your actions.


Once you have finished selecting and release left button mouse, the window will return to its normal state, and the address of the range will appear in the second line. It is calculated from the top left cell to the bottom right cell. Their addresses are separated by the operator “:” - all addresses between the first and last are taken.


Move the cursor to the third line and read from which column the data will be taken if a match is found. In our example it is 3.


Leave the last line blank. By default the value will be 1, let's see what value our function will output. Click OK.


The result is discouraging. "N/A" means invalid data for the function. We didn't specify a value in cell H3, and the function is looking for a blank value.


Enter the name of the month and the value will change.


Only it does not correspond to reality, because the actual actual number of products produced in January is 2000.
This is the effect of the Search Condition argument. Let's change it to 0. To do this, place the pointer on the cell with the formula and press Fx again. In the window that opens, enter “0” in last line.


Click "OK". As you can see, the result has changed.


To check the second condition from the beginning of our article (the function does not search among formulas), let’s change the conditions for the function. Let's increase the range and try to derive a value from a column with calculated values. Enter the values ​​as in the screenshot.


Click OK. As you can see, the search result turned out to be 0, although the value in the table is 85%.


VLOOKUP in Excel “understands” only fixed values.

Comparing data from two Excel tables

VLOOKUP in Excel can be used to compare data from two tables. For example, let’s say we have two sheets with data on product output from two workshops. We can match actual release for both. Let us remind you that to switch between sheets, use their shortcuts at the bottom of the window.

On two sheets we have identical tables with different data.

As you can see, their release plan is the same, but the actual one is different. Switch and compare line by line even for small volumes the data is very inconvenient. On the third sheet, create a table with three columns.

In cell B2 we enter the VLOOKUP function. As the first argument, we will indicate the cell with the month on the current sheet, and select the range from the “Workshop1” sheet. To prevent the range from shifting when copying, press F4 after selecting the range. This will make the link absolute.


Extend the formula to cover the entire column.

Similarly, enter the formula in the next column, only select the range on the “Workshop2” sheet.


After copying, you will receive a summary report from two sheets.

Substituting data from one Excel table to another

This action is performed in the same way. For our example, you don’t need to create separate table, but simply enter the function into a column of any of the tables. Let's show it using the first example. Place the pointer in the last column.


And in cell G3 place the VLOOKUP function. We again take the range from the adjacent sheet.


As a result, the column of the second table will be copied to the first.


That's all the information about the invisible, but useful function VLOOKUP in Excel for dummies. We hope it will help you in solving problems.

Have a great day!