Mrinmoy Roy
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:
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,"")❸ 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:
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)❸ 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 to another cell.
Read more: Sum All Matches with VLOOKUP in Excel (3 Easy Ways)
Similar Readings
- How to Find Case Sensitive Match in Excel ( 6 Formulas)
- How to Match Names in Excel Where Spelling Differ (8 Methods)
- How to Match Data in Excel from 2 Worksheets
- Excel VBA to Match Value in Range (3 Examples)
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:
=INDEX(B5:C12,MATCH(C14,B5:B12,0),2)❸ Press the ENTER button.
␥ 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.
Don’t know if anyone reads these BUT MY MAN YOU DON’T KNOW HOW MUCH TIME YOU SAVED ME.
Thank you so so much.