Sometimes, you may have two sheets that contain related data, but the data is not organized in the same way. For example, you may have one sheet that has product names, prices, and quantities, and another sheet that has product names, categories, and ratings. You may want to combine these two sheets into one, so that you can see all the information for each product in one place.
One way to do this is to use Excel formulas to match and merge the data based on one or more columns that are common to both sheets. In this article, we will show you how to combine two sheets based on 3 columns in Excel formula, using the VLOOKUP, INDEX, and MATCH functions. We will also explain the basic theory behind these functions, and provide a detailed example with real data.
The VLOOKUP function allows you to look up a value in the first column of a table, and return a corresponding value from another column in the same row. The syntax of the VLOOKUP function is:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
where:
- lookup_value is the value that you want to find in the first column of the table.
- table_array is the range of cells that contains the table of data.
- col_index_num is the number of the column in the table that contains the value that you want to return.
- range_lookup is an optional argument that specifies whether you want an exact match (FALSE) or an approximate match (TRUE).
The INDEX function returns a value from a specific cell in a range or an array. The syntax of the INDEX function is:
=INDEX(array, row_num, [column_num])
where:
- array is the range of cells or the array that contains the values that you want to return.
- row_num is the number of the row in the array that contains the value that you want to return.
- column_num is an optional argument that specifies the number of the column in the array that contains the value that you want to return.
The MATCH function returns the relative position of a value in a range or an array. The syntax of the MATCH function is:
=MATCH(lookup_value, lookup_array, [match_type])
where:
- lookup_value is the value that you want to find in the range or array.
- lookup_array is the range of cells or the array that contains the values that you want to match.
- match_type is an optional argument that specifies how you want to match the lookup_value. It can be 0 for an exact match, 1 for a less than or equal to match, or -1 for a greater than or equal to match.
By combining these three functions, you can create a formula that can look up a value in one sheet based on three columns, and return a corresponding value from another sheet. The general formula is:
=VLOOKUP(lookup_value1&lookup_value2&lookup_value3, table_array1&table_array2&table_array3, col_index_num, FALSE)
where:
- lookup_value1, lookup_value2, and lookup_value3 are the values that you want to find in the first, second, and third columns of the table, respectively.
- table_array1, table_array2, and table_array3 are the ranges of cells that contain the first, second, and third columns of the table, respectively.
- col_index_num is the number of the column in the table that contains the value that you want to return.
Alternatively, you can use the INDEX and MATCH functions to create a similar formula, as follows:
=INDEX(return_array, MATCH(lookup_value1&lookup_value2&lookup_value3, lookup_array1&lookup_array2&lookup_array3, 0), [column_num])
where:
- return_array is the range of cells or the array that contains the values that you want to return.
- lookup_value1, lookup_value2, and lookup_value3 are the values that you want to find in the first, second, and third columns of the lookup array, respectively.
- lookup_array1, lookup_array2, and lookup_array3 are the ranges of cells or the array that contain the first, second, and third columns of the lookup array, respectively.
- column_num is an optional argument that specifies the number of the column in the return array that contains the value that you want to return.
Example
To illustrate how to combine two sheets based on 3 columns in Excel formula, let’s use the following example. Suppose we have two sheets, Sheet1 and Sheet2, that contain the following data:
Sheet1:
| Product ID | Product Name | Price | Quantity |
|---|---|---|---|
| A001 | Apple | 2.5 | 100 |
| A002 | Banana | 1.2 | 150 |
| A003 | Cherry | 3.0 | 80 |
| A004 | Date | 4.5 | 50 |
| A005 | Elderberry | 5.0 | 40 |
Sheet2:
| Product ID | Product Name | Category | Rating |
|---|---|---|---|
| A001 | Apple | Fruit | 4.5 |
| A002 | Banana | Fruit | 4.0 |
| A003 | Cherry | Fruit | 3.5 |
| A004 | Date | Fruit | 3.0 |
| A006 | Fig | Fruit | 4.0 |
We want to combine these two sheets into one, so that we can see the price, quantity, category, and rating for each product. We will use the product ID, product name, and category as the three columns to match the data. We will create a new sheet, Sheet3, and use the following formula in cell D5:
=VLOOKUP(A5&B5&C5,Sheet1!$A$5:$D$9&Sheet1!$B$5:$B$9&Sheet2!$C$5:$C$9,4,FALSE)
This formula will look up the value of A5&B5&C5, which is A001AppleFruit, in the first column of the table that is created by concatenating Sheet1!$A$5:$D$9, Sheet1!$B$5:$B$9, and Sheet2!$C$5:$C$9. Then, it will return the value from the fourth column of the same table, which is the price. We can copy this formula to the rest of the cells in column D, and get the following result:
Sheet3:
| Product ID | Product Name | Category | Price |
|---|---|---|---|
| A001 | Apple | Fruit | 2.5 |
| A002 | Banana | Fruit | 1.2 |
| A003 | Cherry | Fruit | 3.0 |
| A004 | Date | Fruit | 4.5 |
| A005 | Elderberry | Fruit | 5.0 |
Similarly, we can use the following formula in cell E5 to get the quantity:
=VLOOKUP(A5&B5&C5,Sheet1!$A$5:$D$9&Sheet1!$B$5:$B$9&Sheet2!$C$5:$C$9,5,FALSE)
We can copy this formula to the rest of the cells in column E, and get the following result:
Sheet3:
| Product ID | Product Name | Category | Price | Quantity |
|---|---|---|---|---|
| A001 | Apple | Fruit | 2.5 | 100 |
| A002 | Banana | Fruit | 1.2 | 150 |
| A003 | Cherry | Fruit | 3.0 | 80 |
| A004 | Date | Fruit | 4.5 | 50 |
| A005 | Elderberry | Fruit | 5.0 | 40 |
Finally, we can use the following formula in cell F5 to get the rating:
=VLOOKUP(A5&B5&C5,Sheet2!$A$5:$D$9&Sheet2!$B$5:$B$9&Sheet2!$C$5:$C$9,4,FALSE)
We can copy this formula to the rest of the cells in column F, and get the following result:
Sheet3:
| Product ID | Product Name | Category | Price | Quantity | Rating |
|---|---|---|---|---|---|
| A001 | Apple | Fruit | 2.5 | 100 | 4.5 |
| A002 | Banana | Fruit | 1.2 | 150 | 4.0 |
| A003 | Cherry | Fruit | 3.0 | 80 | 3.5 |
| A004 | Date | Fruit | 4.5 | 50 | 3.0 |
Alternative Method
Another way to combine two sheets based on 3 columns in Excel formula is to use the INDEX and MATCH functions, as explained in the basic theory section. This method may be more flexible and efficient than the VLOOKUP method, as it does not require sorting the data or concatenating the columns.
To use this method, we will create a new sheet, Sheet4, and use the following formula in cell D5:
=INDEX(Sheet1!$C$5:$C$9, MATCH(A5&B5&C5,Sheet1!$A$5:$A$9&Sheet1!$B$5:$B$9&Sheet2!$C$5:$C$9, 0))
This formula will look up the value of A5&B5&C5, which is A001AppleFruit, in the array that is created by concatenating Sheet1!$A$5:$A$9, Sheet1!$B$5:$B$9, and Sheet2!$C$5:$C$9. Then, it will return the value from the same row in the array Sheet1!$C$5:$C$9, which is the price. We can copy this formula to the rest of the cells in column D, and get the following result:
Sheet4:
| Product ID | Product Name | Category | Price |
|---|---|---|---|
| A001 | Apple | Fruit | 2.5 |
| A002 | Banana | Fruit | 1.2 |
| A003 | Cherry | Fruit | 3.0 |
| A004 | Date | Fruit | 4.5 |
| A005 | Elderberry | Fruit | 5.0 |
Similarly, we can use the following formula in cell E5 to get the quantity:
=INDEX(Sheet1!$D$5:$D$9, MATCH(A5&B5&C5,Sheet1!$A$5:$A$9&Sheet1!$B$5:$B$9&Sheet2!$C$5:$C$9, 0))
We can copy this formula to the rest of the cells in column E, and get the following result:
Sheet4:
| Product ID | Product Name | Category | Price | Quantity |
|---|---|---|---|---|
| A001 | Apple | Fruit | 2.5 | 100 |
| A002 | Banana | Fruit | 1.2 | 150 |
| A003 | Cherry | Fruit | 3.0 | 80 |
| A004 | Date | Fruit | 4.5 | 50 |
| A005 | Elderberry | Fruit | 5.0 | 40 |
Finally, we can use the following formula in cell F5 to get the rating:
=INDEX(Sheet2!$D$5:$D$9, MATCH(A5&B5&C5,Sheet2!$A$5:$A$9&Sheet2!$B$5:$B$9&Sheet2!$C$5:$C$9, 0))
We can copy this formula to the rest of the cells in column F, and get the following result:
Sheet4:
| Product ID | Product Name | Category | Price | Quantity | Rating |
|---|---|---|---|---|---|
| A001 | Apple | Fruit | 2.5 | 100 | 4.5 |
| A002 | Banana | Fruit | 1.2 | 150 | 4.0 |
| A003 | Cherry | Fruit | 3.0 | 80 | 3.5 |
| A004 | Date | Fruit | 4.5 | 50 | 3.0 |
| A005 | Elderberry | Fruit | 5.0 | 40 | #N/A |
Note that the rating for Elderberry is #N/A, because there is no matching product ID, product name, and category in Sheet2. This indicates that the data in the two sheets may not be consistent or complete, and you may need to check and update the data accordingly.
