In this article, we will use a sample product price list as a dataset to demonstrate all the methods.
Method 1 – Use IF Function to Copy Values to Another Cell If Two Cells Match
❶ Select cell E5.
❷ Type the formula:
=IF(B5=C5,D5,"")❸ Press ENTER.
❹ Drag the Fill Handle icon to the end of the Matched Item Price column.
You will see the result in the picture below:
Method 2 – Use VLOOKUP Function to Copy Values to Another Cell If Two Cells Match
❶ Select cell C15.
❷ Type the formula:
=VLOOKUP($C$14,B5:C12,2,1)❸ Press ENTER.
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.
Method 3 – Use INDEX and MATCH Functions to Copy Values to Another Cell If Two Cells Match
❶ Select cell C15.
❷ Type the formula:
=INDEX(B5:C12,MATCH(C14,B5:B12,0),2)❸ Press ENTER.
␥ Formula Breakdown
- MATCH(C14,B5:B12,0) ▶ searches from B5 to B12 to match the values stored in C14. C14 stores the item called Noodles, which is located in 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 indicated by the argument, 2. The INDEX function returns the price in the sixth row, which is 936.
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.
<< Go Back to Excel Match | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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.
Dear Savvas, You are most welcome. Your encouraging words motivate us a lot. Please stay tuned with Exceldemy for amazing contents.
Regards,
MD Tanvir Rahman
Excel and VBA Content Developer
Exceldemy, Softeko
Hi I’m wondering if you can please help me. If two cells match in the same column (cannot be case sensitive), is there a way to copy a few cells from the row of one of the matches to the row of the other match? Thanks so much.
Dear Brittany, Thank you so much for your distinctive query. Here we are setting up a sample dataset of Electronics, Fashion and Snacks category. Using the following formula containing IF function in the D5 cell, you will be able to call the value of cells of the row once it is matched to a cell of same column.
=IF(C5=C$2,D$2,IF(C5=C$3,D$3,IF(C5=C$4,D$4,”Uncategorized”)))
Now drag the Fill Handle tool to fill the cells automatically.
I hope, the solution will be fruitful to you. For any further shortcomings, don’t forget to reach us. Have a good day.
Regards,
MD Tanvir Rahman
Excel and VBA Content Developer
Exceldemy, Softeko