Copy Values to Another Cell If Two Cells Match in Excel

Let’s say that you have a worksheet of product items in two separate columns and their corresponding prices in another separate column. Among the product items, some of them may have duplicate names. Now all your want is to copy the prices of the duplicate items in another cell in your worksheet. If you are facing this problem right now, then go through the whole article. You are going to learn 3 methods to copy corresponding values to another cell if two cells match in Excel.

How to Copy Values to Another Cell If Two Cells Match in Excel: 3 Methods

In this article, we will be using a sample product price list as a dataset to demonstrate all the methods. So, let’s have a sneak peek of the dataset:

So, without having any further discussion let’s dive straight into all the methods one by one.

1. Use IF Function to Copy Values to Another Cell If Two Cells Match

We have some product names under two columns called Itemcat1 and Itemcat2. Within these two columns, there are a few duplicate product names. In the third column, we have the corresponding product prices.

What we are going to do is to copy the price of products that are duplicates. There’s another column called Matched Item Price, where you are going to copy the duplicate product’s prices. We can do all of these things using only the IF function.

So, without having any further discussion, let’s go straight into the procedural steps:

❶ Select cell E5.

❷ Type the formula:

❸ Press the ENTER button.

❹ After that drag the Fill Handle icon to the end of the Matched Item Price column.

When you are done with all the above steps, you will see the result as in the picture below:

2. Use VLOOKUP Function to Copy Values to Another Cell If Two Cells Match

Now we have a search box to search for the duplicate values of items. The search box is called Item. Where you will be inserting any item name listed in the main data table.

So, if our formula finds that there are matchings between two cells in terms of their values then their corresponding price will be copied to another cell.

For example, within the item box, we’ve inserted Noodles. In the Items column of our dataset, there’s already another item called Noodles having a price of $936. So, within the Price box under the Item box, we are going to return this price using the VLOOKUP function.

To perform this operation, all we need to do is,

❶ Select cell C15.

❷ Type the formula:

❸ Press the ENTER button.

After doing all of these, you can see that we’ve successfully copied the price of the Noodles from the main data table.

␥  Formula Breakdown

• $C$14 ▶ contains a lookup value, which is Noodles.
• B5:C12 ▶ range of the whole data table.
• 2 ▶ column index number. This means the price is copied from the second column of the main data table.
• 1 ▶ refers to approx. match between the lookup value and the restored item.
• =VLOOKUP($C$14,B5:C12,2,1) ▶ copies the corresponding values if two cells match another cell.

3. Use INDEX and MATCH Functions to Copy Values to Another Cell If Two Cells Match

In this section, we will be using the INDEX and the MATCH function to copy values to other cells if two cells match in terms of their corresponding values. Now follow the steps below to learn to use these two functions:

❶ Select cell C15.

❷ Type the formula:

❸ Press the ENTER button.

␥  Formula Breakdown

• MATCH(C14,B5:B12,0) ▶ search from B5 to B12 to match the values stored in C14. C14 stores the item called Noodles which is located at the sixth row of the data table. So this function returns 6.
• =INDEX(B5:C12,MATCH(C14,B5:B12,0),2) ▶ looks for the price as indicated by the argument, 2. The price at the sixth row is 936 which is returned by the INDEX function.

Things to Remember

📌 Be careful about the syntax of the functions.

📌 Insert the table range carefully in formulas.

Download the Practice Workbook

You are recommended to download the Excel file and practice along with it.

Conclusion

To sum up, we have discussed 3 methods to copy values to another cell, if two cells match in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP. And please visit our website Exceldemy to explore more.

<< Go Back to Excel Match | Learn Excel