VPR All-Russian verification. Vpr - all-Russian testing work

Batyanov Denis As a guest author, he talks in this post about how to find data in one Excel table and extract it into another, and also reveals all the secrets of the vertical view function.

Using the COLUMN function to specify the extraction column

If the table you are retrieving data into using VLOOKUP has the same structure as the lookup table, but simply contains fewer rows, then VLOOKUP can use the COLUMN() function to automatically calculate the column numbers to retrieve. In this case, all VLOOKUP formulas will be the same (adjusted for the first parameter, which changes automatically)! Please note that the first parameter has an absolute column coordinate.

Creating a composite key using &»|»&

If there is a need to search in several columns at the same time, then it is necessary to create a composite key for the search. If the returned value were not text (as is the case with the “Code” field), but numeric, then the more convenient SUMIFS formula would be suitable for this and the composite column key would not be required at all.

This is my first article for Lifehacker. If you liked it, then I invite you to visit my website, and I will also be happy to read in the comments about your secrets of using the VLOOKUP function and the like. Thank you. :)

The abbreviation VPR (All-Russian Test Work) entered our lives in 2016. “Carry out a VPR”, “Prepare for a VPR”, “Cancel a VPR” sounds almost as familiar as “pass the Unified State Exam”. However, it does not prevent the reader from checking what he knows about VLOOKUP.

After the cancellation of final and intermediate exams (this happened in the 2000s), educational leaders lost the opportunity to make a continuous snapshot of schoolchildren’s knowledge. And only in 2016, in the wake of the success of the National Research on the Quality of Education, Russia decided to fill this gap with the help of uniform tests for the entire country.

Schools, municipalities and regional Departments of Education are recommended to analyze the results of the VPR and clarify whether the knowledge of schoolchildren corresponds to federal state standards.

Everything is fine, and everything is working. But this additional verification becomes a headache for teachers, parents and children.

NOT AN EXAM, BUT MONITORING

The All-Russian test is not an exam, but a monitoring that is carried out to determine the level of preparation of schoolchildren in all regions of Russia.

Therefore, VPR is regulated by Order of the Ministry of Education and Science of the Russian Federation dated January 27, 2017 No. 69 “On monitoring the quality of education.”

This is the youngest of the monitoring studies in education. He is only two years old. But during this time, 95/% of all Russian schoolchildren have already taken part in it.

By the way, saying “pass the VPR” is not accurate. It would be more correct to “write VLOOKUP”.

MANDATORY OR NOT?

VPRs were conceived for voluntary testing of schoolchildren’s knowledge.

Today, VPR subjects are divided into compulsory and optional.

Optional VPR subjects are tested in the testing mode: they are chosen by the school itself to check the level of preparation of students.

In 2018, All-Russian testing is mandatory for grades 4 and 5, but not for grades 6 and 11.

But even in optional subjects, you cannot refuse to participate in the VPR: this decision is made not by the student or his parents, but by the school.

However, in reality, the school does not always choose its own subjects. The most common choice is school.

The decision is made by the regional Department or Ministry of Education. It is they who are tasked by Rosobrnadzor to form a representative sample of schools for conducting the VPR.

For example, in 2018, at least 60% of schools in each constituent entity of the Russian Federation must participate in the All-Russian test in the Russian language for grades 2 and 5.

Regional and municipal officials are instructed to include in this number 10% of urban and the same number of rural educational organizations with the highest and lowest state results. final certification in 9th and 11th grades.

Perhaps parents will ask: who needs the GIA results if second-graders write the VPR?

Just based on the results of the OGE and the Unified State Exam regional authorities determine the level of the school and its place in the regional or local ranking. If your school is ranked among the very best or, conversely, is chronically lagging behind, it may end up on the list of VPR participants in some subjects without even wanting it.

Unfortunately, last year, parents, teachers and directors complained that they were deciding for them (and, most importantly, at the last moment) whether their school would take part in the All-Russian testing work in a particular subject.

WHO COMPLETES VLOOKUP TASKS AND HOW?

This question worries both teachers and parents. Test items common for schools are compiled at the Federal Institute of Pedagogical Measurements (FIPI), taking into account new state standards(FSES).

It is very difficult to obtain official permission to interview the writers of GIA or VPR assignments, even if you are well acquainted with these scientists.

The ideologists of the new type of monitoring say that the benchmark for them was the tasks of the comparative international study PISA, in which schoolchildren from Russia failed at least twice.

PISA questions are focused on practical use school knowledge in real situations. Russian specialists who develop tasks for VPR also strive for this international “bar.”

However, the versions of the All-Russian test works for senior classes also include traditional tasks that experienced teachers have already encountered in demo versions of OGE and Unified State Exam.

“In the demo versions of the VPR, I came across a lot of tasks from the OGE for the 9th grade,” says Ksenia Gennadievna Pudovkina, a teacher of geography and biology at school No. 2 in the city of Sim, Ashinsky district, Chelyabinsk region. - VPR in my subject answer federal standards Therefore, many tasks test not knowledge, but the ability to work with information.”

“If a subject was taught in the system from the 5th to the 11th grades, the student is able to write a VPR on it,” many teachers believe. - Tasks of the All-Russian test work - basic level, no subtleties are used in them.”

“- My children and I solved the demo version of VPR - and no one in the class scored a bad grade. Do the same!”, others advise.

WHICH SUBJECTS OF VPR ARE CONSIDERED THE MOST DIFFICULT: PAY ATTENTION!

Russian language:

Last year, Russian language teachers who were preparing children for a federal test found questions more high level- on topics in grades 6 and 7 that students have not yet covered.

If you are not confident in the children’s knowledge, it is better to open the demo version of VPR on the FIPI website and get acquainted with the tasks. The Russian language is a subject that never hurts to study.

Biology:

Some teachers, during the All-Russian test in biology for grade 5, found in the VPR several tasks on topics that were covered only in textbooks for grades 6 and even 7.

“After this test in biology, the children were very worried,” said one of the mothers. “Some of my daughter’s classmates were one point short of a school C, and some were two points short of a B.” This is a lot of stress for them. And actually almost everything VLOOKUP tasks", which were given to our children, were NOT according to the 5th grade program."

“Currently there is no unified biology program for grade 5,” Rosobrnadzor responded to a request from teachers of this subject. - IN general education organizations RF can be used... work programs of 12 different teams of authors.”

However, if in your VPR school You’ve already taken biology, which means you’re familiar with the problem and have drawn conclusions.

Story:

The second difficult subject of the All-Russian test work was history.

“- The results of the VPR in history showed that schoolchildren in grades 5 and 11 do not know enough about the history of their native land and famous historical figures, do not know how to establish cause-and-effect relationships between historical events and analyze different types sources of historical information,” said Sergei Kravtsov, head of Rosobrnadzor.

On the history course for grade 5, there were indeed questions about the history of our native land. One teacher from small town, for example, analyzed training tasks VPR and figured out that the children would be asked a question about their famous fellow countrymen. His class learned the answer in advance (the famous fellow countryman had only one). And at the VPR the class was asked to name historical event associated with their small homeland. There is no need to explain what the fifth graders wrote instead. The confusion was complete, and the school discussed for a long time whether it was possible to reformulate the VPR question so as not to give the whole class low scores.

Geography:

This year, some students may have difficulties with the geography test: the school must decide for itself whether it will be taken in 10th or 11th grade.

By the way, she can solve it at the last moment (see above).

So that children don’t get nervous, here is a free VPR geography simulator that opens conveniently and gives hints:

HOW ARE THE GRADES ASSIGNED FOR VPR?

“VPR is not only uniform measures and the same tasks that children do all over the country, it is also the same assessment criteria,” emphasizes Sergei Stanchenko, head of the NIKO monitoring research project ( National studies quality of education), which became the predecessors of the VPR.

Federal coordinator of the VPR - Rosobrnadzor ( federal Service for supervision in the field of education and science). It should not be confused with FIPI (Federal Institute of Pedagogical Measurements), which compiles assignments for VPR.

Rosobrnadzor administers the VPR: it appoints regional coordinators of the All-Russian verification work. They become the Departments and Ministries of Education of the constituent entities of the Russian Federation, which form a list of municipal coordinators of the VPR in their constituent entities of the Russian Federation.

Grades for educational tasks are assigned to each student according to a special scale developed by Rosobrnadzor of the Russian Federation.

Then the obtained VPR points are converted into school grades.

If the subject is optional (the school itself chose it in order to test the students’ knowledge), the transfer to grades is carried out at the discretion of the teachers themselves.

Please note: if in your region it has been decided to take into account grades for CDF in the final certification, then they should be taken into account for the entire class, and not for individual students.

It can’t be that someone’s grade for CDF will be credited and put in the journal, but not the other children in the class.

If a CD subject is considered compulsory, it is also assessed by school teachers. But in this case, they must assign points strictly according to the criteria developed by Rosobrnadzor.

These results are entered into electronic form and no later than two days later they are loaded via Personal Area schools on the portal of the All-Russian testing works.

WHAT CAN YOU GET A D IN VPR?

Let's see what you can get a bad mark for on the All-Russian test in mathematics in the 4th grade.

If a student scores from 0 to 5 points for his work, his knowledge is considered unsatisfactory. Scored 6-9 points - you get a C, 10-12 - a B, 13-18 points - rejoice, parents and teachers, you have an excellent student!

Russian language in 4th grade is assessed differently. Anyone who scores less than 13 points gets a two, 14-23 points gets a three, 24-32 points gets a four, 33-38 points gets a five.

This may surprise and disappoint many parents who were convinced that if test- federal, then VPR assignments should be checked in Moscow.

No, the VPR test takes place on site, at school, immediately after students hand in their work. This is not done by the computer, but by the teachers themselves.

“We recommend that our colleagues collectively check several VPR papers,” Sergei Stanchenko explains the verification algorithm, “see what mistakes the students made in them, and then agree among themselves how to evaluate the results in accordance with federal criteria.”

Teachers can familiarize themselves with the regulations for conducting VPR and samples of test work on the official website http://vpr.statgrad.org/

IT'S NORMAL TO WORRY

All-Russian test papers are written on the same day for the entire country. Moreover: even at the same time.

For VPR in the Russian language in grades 2 and 5, it is recommended, for example, to leave lessons 2-3 in the schedule.

All this imposes a certain responsibility on the school administration.

And we need to prepare for the fact that the whole school will be nervous: it’s normal for the whole country to do the same work at the same time.

Some explain to children this way:

“When we wrote the RONO test as children, we were also worried. But for the test we were given a mark in the class register, and the school will simply take your grades into account.”

Some teenagers object:

“Then why try if they don’t put marks on the VPR”?

The answer to this is simple: the VPR scores will be announced in front of the whole class. And this means that those who took the federal test lightly will not have the most pleasant moments.

Plus, based on the results of the VPR, all unsuccessful students may be assigned additional classes.

Do you need this? Same thing.

HOW TO WRITE A VLOOKUP EXCELLENT?

Education leaders claim that it is easy to complete all VPR tasks. You just need to stay in class and don’t skip school.

Teachers who have studied demo versions of VPR in their subject do not always agree with this. They say that only a few students will be able to achieve high scores on federal tests without preparation.

This fact is indirectly recognized by Rosobrnadzor itself, when NOT recommended for teachers (quote):

• specially prepare the class for the implementation of VPR buildings,
• do this during the holidays or after school,
• put grades for the federal test in the journal,
• avoid elements of training for VPR tasks.

How long does it take to prepare for the VPR?

It all depends on the textbook in which the subject is taught, on the class and on the teacher.

“- With children, of course, you will have to repeat everything before the VPR. It took me and my motivated students about three lessons,” says Evgenia Vladimirovna Zhinkina, a physics teacher at school No. 32 with in-depth study in English Ozersk, Chelyabinsk region.

VLOOKUP SCHEDULE

VPR-2018 schedule for 4th grade

• VPR in the Russian language - 04/17/2018 (dictation) and 04/19/2018 (test part);
• In mathematics - 04/24/2018;
• On the subject " The world" - 04/26/2018.

VPR-2018 schedule for grade 5:

• Russian language - 04/17/2018;
• mathematics - 04/19/2018;
• history - 04/24/2018;
• biology - 04/26/2018.

6th grade students will have to write a VPR in testing mode:

• in mathematics - 04/18/2018;
• in biology - 04/20/2018;
• in Russian - 04/25/2018;
• in geography - 04/27/2018;
• in social studies - 05/11/2018;
• in history - 05.15.2018.

The study period for graduates in the 11th grade was postponed to March and April, so as not to increase the stress that arises when preparing for the Unified State Exam.

In 2018, the latest CD in biology for grade 11 will be written on April 12, and the CD in history was postponed to March 21.

11th grade schoolchildren will write VPR using:

• foreign languages - 20.03.2018;
• in history - 03/21/2018;
• in geography - 04/03/2018;
• in chemistry - 04/05/2018;
• in physics - 04/10/2018;
• in biology - 04/12/2018.

VPR IN PRIMARY SCHOOL

In 2nd grades they will take the Russian language, in 4th grades they will take the VPR in the Russian language (dictation and tests), mathematics and the subject “The World around us”. Time to solve problems: 45 minutes

VPR IN BASIC SCHOOL

In the 5th grade, students are expected to study in mathematics, biology, history and the Russian language (twice - in October and April). Since 2018, a CD in history has been added to the mandatory CD in the Russian language. Fifth graders complete assignments for 60 minutes

In the 6th grade, students will have to take the test in the testing mode in the Russian language, mathematics, history, social studies, biology and geography. For 2018, school participation in the VPR for 6th grades is not mandatory. Educational organization She can choose the subject on which she would like to conduct a control cross-section of knowledge.

VPR FOR HIGH SCHOOL

In the 10th grade, students will take the VPR in chemistry and biology. In 11th grade - biology, foreign languages, history, chemistry, geography and physics. VPR is chosen by those graduates who do not pass profile Unified State Exam on this subject. Schools can choose to conduct a course in geography in the 10th or 11th grade. The time for solving problems with VPR for eleventh graders is 90 minutes.

Information and technological support VPR is carried out on the website

Microsoft Excel- an excellent 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.

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 doesn't 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 does the VLOOKUP function work?? The VLOOKUP function in Excel searches your lists of data based on a unique identifier and gives you 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 for the value in the top row of the array ("H" stands for "horizontal").

The VLOOKUP function is available in all versions of Excel 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 may contain different meanings, 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

In practice, VLOOKUP formulas are rarely used to search for data on one 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 name of the worksheet and an 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're going 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 - VLOOKUP Example with Multiple 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 doesn't 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. The tables must be located in the same 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

The VLOOKUP syntax in Russified Excel is:

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.


The VLOOKUP window for the Excel table appears.


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.


As soon as you finish the selection and release the left mouse button, the window will return to its normal condition, 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 haven't specified 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 the 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 compare the 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. Switching and comparing row by row even for small amounts of 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 can not create a separate table, but simply enter a function in 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!