How to match data in two tables in excel

There are two methods to correspond to data in different tables in Excel: use the VLOOKUP function to search within the search range based on the search value and return the corresponding data. Use the INDEX and MATCH function combination to return values ​​within a specified range by matching lookup values.

How to correspond to data in different tables in Excel

How to correspond to data in different tables in Excel Two common methods:

Method 1: Using the VLOOKUP function

The VLOOKUP function is used to find a specific value in a table and return the data corresponding to that value. The syntax is:

<code>=VLOOKUP(查找值, 查找范围, 返回列, [近似匹配])</code>

Among them:

• Find value: The value that needs to be found in the search range.
• Lookup range: The table containing the value to be looked up.
• Return column: Specify the column where the data corresponding to the lookup value is to be returned.
• Approximate matching (optional): Specify whether to use approximate matching, the default value is FALSE.

Example:

Suppose there are two tables, "Employee Information" and "Sales Records", which need to correspond to the employee names in each sales record:

Employee information	Sales records
Name	Employee Number		Sales Date	Sales Record
John Doe	1		2023-01-01	100
Jane Smith	2		2023-01-02	150
Peter Davis	3		2023-01-03	200

In the "Sales Record" table, you can use the following VLOOKUP function to Corresponding employee name:

<code>=VLOOKUP(A2, '员工信息'!A:B, 2, FALSE)</code>

This function will find the employee number corresponding to the sales date in the "Employee Information" table based on the employee number, and return the employee's name.

Method 2: Use the INDEX MATCH function

The combination of INDEX and MATCH functions can also be used to correspond to data in different tables. The INDEX function returns a value within a specified range, while the MATCH function finds the position of a specified value within a specified range. The syntax is:

<code>=INDEX(返回范围, MATCH(查找值, 查找范围, [近似匹配]))</code>

Among them:

• Return range: Specify the table to return data.
• Search value: The value that needs to be found within the search range.
• Lookup range: The table containing the value to be looked up.
• Approximate matching (optional): Specify whether to use approximate matching, the default value is FALSE.

Example:

For the above example, you can use the following combination of INDEX and MATCH functions to correspond to employee names:

<code>=INDEX('员工信息'!A:A, MATCH(A2, '员工信息'!B:B, 0))</code>

This function will Find the employee number in column A of the "Employee Information" table that matches the employee number in the "Sales Records" table, and return the employee's name.

The above is the detailed content of How to match data in two tables in excel. For more information, please follow other related articles on the PHP Chinese website!