How to Find Matching Strings and Copy Data in Excel

Sometimes, you may need to find matching strings in one column and copy data from adjacent columns to its own column in Excel. For example, you may have a list of products and their prices in one sheet, and a list of orders and their quantities in another sheet. You may want to find the matching products in both sheets and copy the prices and quantities to a new sheet.

There are different ways to achieve this task in Excel, such as using formulas, functions, or tools. In this article, we will explain the basic theory behind finding matching strings and copying data, and show you how to use the VLOOKUP function and the Advanced Filter tool to do it.

To find matching strings in one column and copy data from adjacent columns to its own column, you need to perform two steps:

1. Lookup: This step involves finding the matching strings in the lookup column, which is the column that contains the values you want to search for. For example, if you want to find the matching products in both sheets, the lookup column is the column that contains the product names.
2. Copy: This step involves copying the data from the adjacent columns to the destination column, which is the column where you want to paste the data. For example, if you want to copy the prices and quantities to a new sheet, the destination column is the column where you want to paste the prices and quantities.

VLOOKUP Function

One way to find matching strings and copy data in Excel is to use the VLOOKUP function. The VLOOKUP function allows you to look up a value in the first column of a table and return a 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])

The arguments of the VLOOKUP function are:

• lookup_value: The value you want to look up in the first column of the table. This can be a cell reference, a text string, or a number.
• table_array: The range of cells that contains the table of data. The first column of the table must contain the lookup values.
• col_index_num: The column number in the table from which you want to return a value. The first column is 1, the second column is 2, and so on.
• range_lookup: An optional argument that specifies whether you want to find an exact match or an approximate match. If you omit this argument or set it to TRUE, the function will find the closest match that is less than or equal to the lookup value. If you set it to FALSE, the function will find an exact match or return an error if no match is found.

To use the VLOOKUP function to find matching strings and copy data, you need to follow these steps:

1. Prepare the data: Make sure that the data you want to look up and copy are in a table format, with the lookup values in the first column and the data you want to copy in the other columns. You can name the table ranges for easier reference. For example, you can name the range of cells that contains the products and prices as Products, and the range of cells that contains the orders and quantities as Orders.
2. Enter the formula: In the destination column, enter the VLOOKUP formula that references the lookup value, the table array, and the column index number. For example, if you want to copy the prices from the Products table to the Orders table, you can enter the formula =VLOOKUP(A2,Products,2,FALSE) in cell B2 of the Orders table, where A2 is the lookup value, Products is the table array, 2 is the column index number, and FALSE is the range lookup. This formula will look up the product name in cell A2 of the Orders table, find the matching product name in the first column of the Products table, and return the price from the second column of the Products table.
3. Copy the formula: Copy the formula down the destination column to fill the rest of the cells. The VLOOKUP function will automatically adjust the cell references to match the lookup values in each row. For example, if you copy the formula from cell B2 to cell B3 of the Orders table, the formula will change to =VLOOKUP(A3,Products,2,FALSE), which will look up the product name in cell A3 and return the price from the Products table.

Advanced Filter Tool

Another way to find matching strings and copy data in Excel is to use the Advanced Filter tool. The Advanced Filter tool allows you to filter a range of data based on complex criteria and copy the filtered data to another location. To use the Advanced Filter tool to find matching strings and copy data, you need to follow these steps:

1. Prepare the data: Make sure that the data you want to look up and copy are in a table format, with the lookup values in the first column and the data you want to copy in the other columns. You also need to create a criteria range, which is a range of cells that contains the criteria for filtering the data. The criteria range must have the same column headers as the data range, and the criteria must be entered below the headers. For example, if you want to filter the Products table based on the product names in the Orders table, you can create a criteria range in cells F1:G2, where F1 is the header Product, and F2 is the formula =Orders!A2, which references the first product name in the Orders table.
2. Select the data: Select the range of cells that contains the data you want to filter and copy. For example, select the Products table in cells A1:B6.
3. Open the Advanced Filter dialog box: On the Data tab, in the Sort & Filter group, click Advanced. The Advanced Filter dialog box will appear.
4. Choose the action: In the Action section, choose whether you want to filter the data in place or copy the data to another location. If you choose to filter the data in place, the original data will be hidden and only the filtered data will be visible. If you choose to copy the data to another location, the original data will remain unchanged and the filtered data will be copied to a new range of cells. For example, choose to copy the data to another location.
5. Specify the criteria range: In the Criteria range box, enter or select the range of cells that contains the criteria for filtering the data. For example, enter or select F1:G2, which is the criteria range we created earlier.
6. Specify the destination range: In the Copy to box, enter or select the range of cells where you want to paste the filtered data. Make sure that the destination range has enough rows and columns to accommodate the filtered data, and that it does not overlap with the data range or the criteria range. For example, enter or select I1:J6, which is an empty range of cells that has the same size as the Products table.
7. Click OK: Click OK to apply the filter and copy the data. The filtered data will be copied to the destination range, and the original data will remain unchanged. For example, the Products table will be filtered based on the product names in the Orders table, and the matching products and prices will be copied to the range I1:J6.

Example

To illustrate how to find matching strings and copy data in Excel, let us use a simple example. Suppose we have two sheets, Sheet1 and Sheet2, that contain the following data:

We want to find the matching products in both sheets and copy the prices and quantities to a new sheet, Sheet3. We can use either the VLOOKUP function or the Advanced Filter tool to do this.

Using VLOOKUP Function

To use the VLOOKUP function to find matching products and copy prices and quantities, we can follow these steps:

1. Prepare the data: We can name the range of cells that contains the products and prices in Sheet1 as Products, and the range of cells that contains the orders and quantities in Sheet2 as Orders.
2. Enter the formula: In Sheet3, we can enter the formula =VLOOKUP(A2,Products,2,FALSE) in cell B2, where A2 is the lookup value, Products is the table array, 2 is the column index number, and FALSE is the range lookup. This formula will look up the product name in cell A2 of the Orders table, find the matching product name in the first column of the Products table, and return the price from the second column of the Products table.

3. Copy the formula: We can copy the formula down the columns B and C to fill the rest of the cells. The VLOOKUP function will automatically adjust the cell references to match the lookup values in each row.

The result will look like this:

Note that the product name Pear does not have a matching value in the Products table, so the VLOOKUP function returns an error value #N/A in cell B4. We can use the IFERROR function to handle this error and display a blank or a custom message instead. For example, we can enter the formula =IFERROR(VLOOKUP(A2,Products,2,FALSE),"Not found") in cell B2 and copy it down the column B. This formula will check if the VLOOKUP function returns an error, and if so, display the text Not found. Otherwise, it will display the result of the VLOOKUP function.

The result will look like this:

Using Advanced Filter Tool

To use the Advanced Filter tool to find matching products and copy prices and quantities, we can follow these steps:

1. Prepare the data: We can create a criteria range in cells F1:G6 of Sheet1, where F1 is the header Product, and F2:F6 are the formulas =Orders!A2, =Orders!A3, =Orders!A4, =Orders!A5, and =Orders!A6, which reference the product names in the Orders table.
2. Select the data: We can select the range of cells that contains the products and prices in Sheet1, which is A1:B6.
3. Open the Advanced Filter dialog box: We can click the Advanced button on the Data tab, in the Sort & Filter group. The Advanced Filter dialog box will appear.
4. Choose the action: We can choose to copy the data to another location.
5. Specify the criteria range: We can enter or select F1:G6, which is the criteria range we created earlier.
6. Specify the destination range: We can enter or select A1:B6 of Sheet3, which is an empty range of cells that has the same size as the Products table.
7. Click OK: We can click OK to apply the filter and copy the data. The filtered data will be copied to the destination range, and the original data will remain unchanged.

The result will look like this:

Note that the product name Pear does not have a matching value in the Products table, so the Advanced Filter tool does not copy any data for it. The destination range will have a blank row in row 4.