How to match information from two tables in Excel

Excel provides two common methods to match information from two tables: Use the VLOOKUP function to perform a row lookup and return the value of another column. Use a combination of MATCH and INDEX functions to find the location of a value and then obtain the corresponding value.

How to use Excel to match information from two tables

Excel provides a variety of methods to match information between two tables. To match the information in a table, two common methods are introduced below:

1. Use the VLOOKUP function

The VLOOKUP function is used to find the specified row by row in the table. value and then returns the value of another column in that row. The syntax is as follows:

<code>VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])</code>

Where:

• lookup_value: The value to be found
• table_array: Contains the value to be looked up Table of values ​​
• col_index_num: The index number of the column where the value to be returned is
• range_lookup: Optional parameter, specify the search range. TRUE is approximate matching, FALSE is exact matching

Example:

Suppose there are two tables, Table 1 and Table 2, and you want to match the Customer ID with order details from Table 2:

##Customer IDOrder number100112345##10021003In Table 1, enter the following in cell B2 VLOOKUP Function:

Table 1	Table 2
23456
34567

<code>=VLOOKUP(A2, Table2!A:B, 2, FALSE)</code>

This will return order number 12345 corresponding to customer ID 1001.

2. Using the MATCH and INDEX functions

The MATCH function is used to find a specified value in a table and return its position. The INDEX function is used to obtain the value at a specified position in a table. These two functions are used together to perform matching.

Example:

Using the above example, you can enter the following formula in cell B2 in Table 1:

<code>=INDEX(Table2!B:B, MATCH(A2, Table2!A:A, 0))</code>

This will return the value corresponding to Order number 12345 for customer ID 1001.

Note:

Make sure the column data types to be matched are the same.

• For approximate matching, use TRUE as the last parameter of the VLOOKUP function.
• For an exact match, use FALSE.
• If no match is found, the VLOOKUP function returns a #N/A error.

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