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

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 you 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. Because you are going to learn 3 methods to copy corresponding values to another cell if two cells match in Excel.


Download the Practice Workbook

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


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

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:

Sample Dataset for 3 Methods to Copy Values to Another Cell If Two Cells Match in Excel

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:

=IF(B5=C5,D5,"")
within the cell.

❸ Press the ENTER button.

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

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

Fill Handle

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

Result of IF Function to Copy Values to Another Cell If Two Cells Match

Read More: Excel VBA: Copy Row If Cell Value Matches (2 Methods)


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:

=VLOOKUP($C$14,B5:C12,2,1)
within the cell.

❸ 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.

The VLOOKUP Function

␥  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 to another cell.

Read more: Sum All Matches with VLOOKUP in Excel (3 Easy Ways) 


Similar Readings


3. Use INDEX and MATCH Function 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:

=INDEX(B5:C12,MATCH(C14,B5:B12,0),2)
within the cell.

❸ Press the ENTER button.

Usage of the INDEX and the MATCH function

␥  Formula Breakdown

  • MATCH(C14,B5:B12,0) ▶ search from B5 to B12 to match for 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.

Read More: Excel Find Matching Values in Two Columns


Things to Remember

📌 Be careful about the syntax of the functions.

📌 Insert the table range carefully in formulas.


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.


Related Articles

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo