Excel uses the VLOOKUP function to count from one table to another

Excel table processing data is very powerful, in the work, often need to look up from the database file index data to another file, how to implement this reference? by VLOOKUP function, you can do it easily.

The VLOOKUP function is a portrait lookup function that is used to find indexed data by column. We can do it quickly according to the requirements.

Steps

Open the computer, click Excel, open the database file and the file that needs to refer to the data.

A copy of the electronic books is now used as an example: Enterprises in the customs record 552 different goods, the 552 "Commodity name" has "record serial number", the other part of the material table file only "record serial number", we want to according to "record serial number" from the total material data to lead the required "commodity name."

Click the "Commodity name" column you want to refer to with your mouse, enter the "=" number, and the equal sign appears in the upper section of the formula bar.

The left side of the formula bar will have a small button, with the mouse click, display a Drop-down menu, from which to select "VLOOKUP".

Select "VLOOKUP", a function parameter view window jumps out.

There are 4 parameters that need to be set: Lookup_value,table_array,col_index_num,range_lookup.

Lookup_value, refers to the data values that need to be searched in the datasheet.

Now you need to search with a "record number" as the condition. Use the mouse to select all of the data in this column to return. The file "record number" that you need to refer to IS in E3 column to E24 column, all selected.

Table_array, refers to the data table in which you want to search for data. This is the database that represents the reference.

Return to the database file and select the entire table to return.

Col_index_num refers to the column in which the data value is referenced when the data to be searched is found in the database file.

"Record serial number" in the database, find the corresponding "Commodity serial number", to the third column of "Commodity name" returned, so here we fill in the example of the numerical value can be.

For example, the product serial number is "1", the Database, "1" corresponding to the product name is "with the connector Wire", located in the 3rd example of the database, this is the data we will return to the number of columns, the "3" fill in as can.

Range_lookup refers to whether the value of the lookup matches the exact or approximate match in the database or, if False, indicates an approximate match, or, if TRUE or omitted, an exact match. We need an exact match here, so fill in true. All set the completion point "OK" can be.

8

When the setting is complete, the quoted data value appears in the "Commodity name" column of the referenced table.

Hold the CTRL key in hand, select the referenced data value, and drag down to all the columns you want to index.

All of the data are quoted here.

Then the reference data values are all selected to copy (copy shortcut key: Ctrl + C), click the right mouse button, an interface appears, select "Paste Selective".

Select value, and the operation Bar selects None. Click "OK" to complete.

Attention matters

第8-10 steps, copy and paste: After the function, although the display is a numerical value, but click in each column is a formula, you need to copy the "value" paste, so that click into each column will not show are formulas, but the value.

function Argument dialog box when each column parameter is selected, if automatically becomes a single column, click the Red Small logo on the right.

The above is Excel through the VLOOKUP function from a table to another table method introduction, we follow the steps above to operate it, I hope this article can help!