How to use formulas to make excel data match

First of all, you can get the data you want, and if you want to eliminate the data you don't need, then you need to use the vlookup function in Excel, and the specific operation is as follows:

1. Now there are two tables, for example, the requirement is: to match the "quantity" in the first table to the second table.

<>2. Now start using the vlookup function, enter =vlookup() in the determined cell and column values, and 4 parameters will be displayed here.

3. For the first parameter, find two tables, the same column value, and put a comma after the parameter.

4. The second parameter, starting from the same parameter in the second table, select the area of the value you want, and put a comma after the parameter.

5. The third value should be the number of values in the area, and the third parameter should be written on it.

6. For the fourth parameter, select - exact match - double-click, and then put the brackets on it.

7. After entering, pull down again, you can see the desired value, and the one with "n a" is not matched.

Take the question as the content (the subject of the email must contain "excel", I will use this as a basis to identify non-spam emails, so as not to delete them by mistake), and send the excel sample file (showing the current situation and target effect) as an attachment to read it.

It is a detailed operation process:

If the data to be matched is in two different **, then you need to open two ** first, where we call the ** to fill the data [Filling**], and the ** that needs to import the data is called [Data Source**]. First of all, we select the cells to fill the data in the filling**, click the [Formula] option at the top of the main page, and then click the [Insert Function] in the function bar to insert the matching function for Excel**.

Search for the function vlookup at the top of the function page, and then click the function in the search results to enter the specific settings page of the function, there are four rows of data values that need to be entered on the page, and all of them must be entered accurately to match the data of the two **.

There is a column of data in the two ** should be the same data, so that the function can fill the corresponding result according to the same data, enter the column coordinates of the same data in the first row of the function, you only need to select all the columns and enter them into the first row.

For example, in this example, you want to search for the age of the person, and the common column of the two tables is the name, then you need to select the column coordinates from the name to the age.

In the third row of the function, enter the number of columns from the same item to the filled item selected in the previous step, if there are two items between name and age in the data source ** in this example, then you need to fill in 2, and finally fill in 0 in the fourth row and click OK at the bottom of the page to match the data in the two **.

In this case, we only set the matching data of one cell, if you want to match the whole column of data, you only need to drag the cell to apply the functions in the cell to other rows.

On the data page that opens, click Select Find Entry. In the dialog box that pops up, click the icon behind the table to select the data to be entered, and click Next after entering the corresponding data. Click the icon behind the select data source, and select and specify the appropriate search column and result column according to your needs in the dialog box, detailed steps:

1. In the open excel**, click the matching sheet2**.

2. In the new summary sheet work page, click Data in the menu.

3. In the data page that opens, click Select Find Entry.

4. In the pop-up search and input dialog box, click the icon behind the data table to be entered, and click Next after entering the corresponding data.

5. Click the icon behind the selected data source, and click Next after entering the corresponding data.

6. In the dialog box, select and specify the appropriate search column and result column according to the usage requirements.

7. After successful selection, you can complete the setting.

If you have one table per person, you can use VBA to implement it. If it's a single table, you can use vlookup to match names.

1. Open the excel document and enter the relevant data.

2. Double-click the cell where the result is entered, and enter vlookup=.

3. Enter the matching cells, the cells to be found, the returned values and the matching conditions in the parentheses, and press the enter key after the input is completed.

4. Place the mouse in the lower right corner of the first matching data frame, after the mouse becomes a cross, hold down the left mouse button and drag it down, and other data will appear.

This article is based on the Dell Inspiron 5000 brand, Windows 10 system, Microsoft Office Excel 2019 version.

Vlookup is a very resent data matching tool in excel, if you will be proficient in using this function, you can greatly reduce your workload, the following is a tutorial on the use of excel matching formula shared by me for everyone to read and learn.

Excel Matching Formula Tutorial:

<> the matching formula uses step 4: this step is the most important, set the vlookup function, and the operation steps are shown in the figure;

First, as shown in the figure below, enter the formula =vlookup(b2,<> in cell C2 as shown in the figure below, then click Table 1 (i.e., sheet1), select b2:d11 in table 1, and form =vlookup(b2,sheet1!).b2:

D11<> third as shown in the figure below, enter the full formula =vlookup(b2,sheet1!b2:d11,2),and then pull down to the right to get the following effect.

Have a great weekend!

Figure 1 is in sheet1, and the formula is in sheet2, i.e., Figure 2, sheet2 (i.e., Figure 2) c1:

vlookup($a2,sheet1!$a:$d,column(c1),0)

Pull one square to the right and one more to see if that's the case.

You can directly use the vlookup function to achieve the purpose, the formula is an array formula, copy and paste and press the ctrl shift enter three keys at the same time, you can use it.

The specific formula is: =vlookup(g2,a:d,,0).

<>iferror(vlookup(left(e2,2),$a$1:$c$6,2,0),vlookup(left(a2,1),$a$1:$c$6,2,0))

iferror(vlookup(left(e2,2),$a$1:$c$6,3,0),vlookup(left(a2,1),$a$1:$c$6,3,0))

The formula has been attached, so give it a good review.

According to the current data, the data on the right is the name of column A on the left with 2 digits, so the formula for f2 and g2 is:

f2=vlookup(left(e2,len(e2)-2),a:b,2,0)

G2=VlookUp(F2,B:C,2,0) Then, select F2:G2 cell, copy and pull down, you can .........