PLEASE ADVISE ON THE USE OF VLOOKUP IN EXCEL, THE MORE DETAILED THE BETTER. Thank you

About the application of the vlookup function in Excel.

Ladies and gentlemen, I want to ask, when I used the vlookup function, because the data is not too much, as long as you manually pull it down, the following cells will be automatically applied, and now there are more and more data, and the pull-down method is not tried, who has any other way? Thank you.

Definitely yes.

Did you specify the number of rows in the lookup area in the original formula?

For example, vlookup(a1,$c$1:$e$100,2,0).

Such? You modify the lookup area to not specify a row.

vlookup(a1,c:e,2,0)

Such? Follow-up hello, now there is a problem with vlookup(a1,c:e,2,0) this is no problem. But I'm not just A1 to compare, but also A2, A3....Can I set up a region? Thank you.

Yes. Your formula pull-down will do, it's a must.

If you find it troublesome to pull down, you can enter the formula all at once.

For example, if you want to compare A1:A10000.

Let's select A1:A10000 first

This one is easy, right?

Then enter directly.

if(a1="","",vlookup(a1,c:e,2,0))

When you're done typing, don't press enter.

Instead, press Ctrl+Enter.

I'm sorry to ask, I'm a newbie, I don't understand it too much. I am now in Sheet 1 with data 1 to 10000 in column A, and I want to compare the data from 1 to 60000 in column A in Sheet2. Excuse me, how to write this formula?

Thank you very much! I wanted to communicate in hi, but it seems that you don't need hi

What is the contrast? Compare which data in sheet 2 table 60000 is not in sheet 1?

Select column B of the sheet2 table, that is, click the column label of column B.

if(countif(sheet1!a:a,a1)>=1,"",a1)

Then press Ctrl + Enter.

In this way, the value shown in column b in the sheet2 table means that there is no such thing in the sheet1 table. Otherwise, it will be displayed as empty.

Excel** is one of the most used software in daily work, and its strong data processing function makes it impossible not to use it, and there are a large number of functions that can help us process data quickly. For example, there is a function called vlookup function in Excel, which is a longitudinal lookup function. vlookup is one of the most important functions in excel that can help us find the data we want in a lot of data.

Let's show how to use the vlookup function. The use of vlookup is something that everyone has to master, if you want to learn more about the use of functions, you can go on, there is a special excel function tutorial**, explain in detail, and teach the package.

Tutorial on how to use the excel function vlookup:

2. Select a space to add a filter value and click the [FX] button at the top.

4. Add the value you want to find in [Lookup Value], here it is [A4].

5. Then add the range of data search in [Table Array], here it is [A2:B6].

6. Add the column number of the return cell of the whole function to [col index num], which is here.

7. Finally, add an exact match in [Range Lookup], which is false here.

8. Click [OK], at this time, you will see that the value you want to find has appeared in the [Space].

The above is the tutorial process for the use of the vlookup function, it is not complicated, and you need to practice more if you want to master the use of this function.

vlookup

The vlookup function in excel can help us quickly match the desired content from the existing data, as shown in the figure below, as long as there is the same item on the right and the same item on the left, we can quickly match the "work number" and "group" through the same item "name". In this case, we need to quickly match the "job number" through the "name".

Step 1 Fix the mouse cursor in the cell to match the job number (as shown in Figure 2 H2), click "Formula-Insert Function" on the toolbar, then the insert function window will pop up, select the vlookup function, click "OK" (as shown in Figure 3), the vlookup function window will pop up (as shown in Figure 4).

1. lookup value refers to what is the same item in the same matching result, because in this case, we match the "work number" by "name" as the same item, so the "g2" cell should be selected here;

2. Table array refers to the matching data, and the data in this case should be the previous one, so the selection range is "b:c", that is, column B to column C. Note that in the process of using the vlookup function, the same item must be in the first column in the selected data source, so in this case, the selection starts directly from column b, instead of column a;

3. col index num refers to the number of columns in the selected data**, the result to be found in this case is "work number", and it is in the second column in the selected "b:c" data**, so fill in the data "2";

4. Range lookup refers to whether the search is to be matched exactly or roughly, and 0 or false is commonly used for exact matching; Roughly match 1 or true. The exact match is a complete search, and if it is not found, it will return the wrong value n a; The approximate match is first found to be exactly the same, and then the value is very close when it cannot be found, and the wrong value n a has to be passed back if it is not found. In this case, we need an exact match, so the fourth entry is 0.

After completing the above input, click the "OK" button in the lower right corner of the window, and the result will appear in the cell where the cursor is located (H2), and the result is J3923, which is exactly the same as the job number in the data source.

Step 3 Select the H2 cell, fix the mouse cursor in the lower right corner of the cell, until the cursor changes from a hollow cross to a black solid cross, then double-click the right mouse button, Excel will find and fill in the work numbers of the remaining cells according to the formula and format of H2.

How do I use the vlookup function in Excel? The following is what I brought about the use of vlookup function in Excel, I hope it will inspire you after reading it!

Usage of vlookup function in excel:

Step 2 of using the vlookup function: Next, our task is to find the grade of student C by using the vlookup function. To do this, enter "=vlookup" in the cell, and you will find that vlookup includes three parameters and one optional parameter.

The vlookup function uses step 3: where "lookup value" refers to the value to be found, which in this case is "c".

Step 5: The third parameter "col index num" refers to the column number where the whole function returns the cell, which is "2" in this case.

The vlookup function syntax has the following parameters (parameters: values that provide information about an action, event, method, property, function, or procedure. ）：

If the value provided for the LookUp Value parameter is less than the minimum value in the first column of the Table Array parameter, VLOOKUP will return an error value of N a.

table array required. The range of cells that contain the data. You can use a pair of zones (for example, A2:

d8) or a reference to the name of the region. The value in the first column of the table array is the value searched by the lookup value. These values can be textual, numeric, or logical.

The text is not case sensitive.

col index num required. The column number of the matching value that must be returned in the table array parameter. When the col index num parameter is 1, the value in the first column of the table array is returned; When col index num is 2, the value in the second column of the table array is returned, and so on.

1. Open ** with data, and find a data as an example, as shown in the figure, at least two rows of data are included, so as to ensure that there is data to reference. As shown in the figure, there are five columns of data, and the more complex it is, the more data it contains.

2. The purpose of data numbering is to ensure the uniqueness of the data, such as the area where the product with the same name is not the same, and the uniqueness of the data should be ensured in order to ensure the accuracy of the data searched.

3. To find out if the quilt at point B of library A is not there, it is necessary to find out whether the product numbered 001 is in the warehouse. Open a new page sheet2 and use the number to find the reference. And a good table line type head,"Number"with"Whether it is in the library"。

Also make sure that the data cited on the first page is numbered first and the results are last.

4. Enter =vlookup in the cell corresponding to the number directly below the library to find the reference in the first step.

5. Enter the area where the answer to the cited data is located. This is to enter the second parameter. In the following example, it is required to include both the reference data and the result data. and enter a comma with an open and missing English at the end of the range.

6. The last data is to ask whether it is to find exactly, if it is, you can enter the number 0. You can also enter false, which means the same thing. If it's a fuzzy lookup, enter 1 or true.