You have come to the right place if you are looking for the answer or some unique tips to copy another cell if a value exists in a column in Excel. There are 3 ways to copy another cell if a value exists in a column in Excel. This article will walk you through each and every step with appropriate examples. As a result, you can use them easily for your purpose. Let’s move on to the article’s main discussion.
If Value Exists in Column Then Copy Another Cell in Excel: 3 Quick Ways
In this section, I will show you the quick and easy steps to copy another cell if a value exists in a column in Excel on the Windows operating system. This article contains detailed explanations with clear illustrations for everything. I have used the Microsoft 365 version here. However, you may use any other version depending on your availability. Please leave a comment if any part of this article does not work in your version.
Here, I have a dataset of Code numbers of products, product names, colors, sizes, and prices. I want to check a code whether exists in the column and if exists then I will copy the price of that product.
1. Using Filter Command
For this, I will use the Excel Filter option to search for the value.
📌 Steps:
- First, select the Column Header of Code column.
- Then, go to the Home tab and select the Filter Option under the Sort & Filter drop-down.
- As a result, there will create a filter icon on each header of the data table.
- Then, click on the filter arrow of the Code column and there will open a context menu.
- Under the Text Filters menu, select only the cell that you want to get.
- Alternatively, for large data, you can search that item in the Search box under the Text Filter menu.
- As a result, you will see that row only that is visible and other rows become hidden.
- Now, you can use the Ctrl+C shortcut to copy other cell values of the row.
Read More: How to Copy and Paste in Excel When Filter Is On
2. Using FILTER Function
Alternatively, you can use the FILTER function to do the same thing. Follow the steps below.
📌 Steps:
- First, insert the value for which the function will search in cell I5.
- Then, insert the following formula in cell H6:
=FILTER(B5:F20,B5:B20=I3)
🔎 Formula Breakdown:
Syntax of FILTER Function:
=FILTER (array, include, [if_empty])
- Array – B5:F20: This is the range where the FILTER function will search for.
- Include – B5:B20=I3: This is the criteria which if met than the FILTER function will bring the whole row.
Read More: How to Copy Cell If Condition Is Met in Excel
3. Using VBA Code
Alternatively, you can use a VBA code to do the same thing. Follow the steps below for this:
📌 Steps:
- For this, first, go to the top ribbon and press on the Developer, then press on the Visual Basic option from the menu.
- You can use ALT + F11 to open the “Microsoft Visual Basic for Applications” window if you don’t have the Developer tab added.
- Now, a window named “Microsoft Visual Basic for Applications” will appear. Here from the top menu bar, press on the “Insert” And a menu will appear. From them, select the “Module’” option.
- Now, a new “Module” window will appear. And Paste this VBA code into the box.
Sub CopyBudgetRecords()
Dim copyrng As Range
Dim cell As Range
Dim PasteRng As Range
Set copyrng = Sheet1.Range("B5:B20")
For Each cell In copyrng
Set PasteRng = Sheet4.Range("A5")
If cell = "C_O_M" Then Range(cell.End(xlToLeft), cell.End(xlToRight)).Copy PasteRng
Next cell
End Sub
🔎 VBA Code Breakdown:
Sub CopyBudgetRecords()- Setting the Sub name for the VBA code.
Dim copyrng As Range
Dim cell As Range
Dim PasteRng As Range- Specifying the variables that will be used in the code.
Set copyrng = Sheet1.Range("B5:B20") - Setting up the cell range where I will search for specific cell value.
For Each cell In copyrng
Set PasteRng = Sheet4.Range("A5")- Setting cells where to paste the values.
If cell = "C_O_M" Then Range(cell.End(xlToLeft), cell.End(xlToRight)).Copy PasteRng - If any cell of range B5:B20 matches with value “C_O_M” then copy the full row.
Next cell
End Sub- Making the end of For loop and the Sub
- To run the code go to the top menu, press on the Run option, and here will open some other options and select the Run Sub/UserForm also you can simply press F5 to run the code.
- As a result, you will see the cell range B5:F5 is filled with values that are the copy of the selected dataset.
How to Lookup Value in a Column and Return Value of Another Column in Excel
The business world deals with massive datasets. Microsoft Excel has made their job easier by providing various formulas for easily maintaining datasets. However, the most common thing in dataset maintenance is looking up values in a column and getting the return of another column of related data. I will show you how you can do this:
📌 Steps:
- Here, I have a dataset of Code numbers of products, product names, colors, sizes, and prices.
- Now, I want to use a lookup value of the product code inserted in cell I5 and search for this cell in the dataset and get the return of the price.
- For this, I have used the following formula in cell I6:
=VLOOKUP(I5,B5:F20,5,FALSE)
🔎 Formula Breakdown:
Syntax of VLOOKUP function:
=VLOOKUP(lookup_value, table_array,col_index_num, [range_lookup])
Here,
- lookup_value=I5: The VLOOKUP function will search for this value in the 1st column of the selected data range.
- Table_array = B5:F20: it is the range of the dataset where the function will work.
- Col_index_num = 5: it is the column index number of the column from where we want to extract the result.
- Range_lookup = FALSE: it is whether the result will be an exact or partial match. You will notice the above-mentioned types of VLOOKUP formula differ only in this parameter taking FALSE for the exact match and TRUE for the partial match.
- Thus, you can easily get the return from another column for matching a cell using the VLOOKUP function.
Download Practice Workbook
You can download the practice workbook from here:
Conclusion
In this article, you have found how to copy another cell if a value exists in a column in Excel. I hope you found this article helpful. Please leave comments, suggestions, or queries if you have any in the comment section below.
Related Articles
- How to Copy Horizontal and Paste Vertical in Excel
- How to Copy Paste Vertical to Horizontal in Excel
- How to Copy and Paste in Excel with Merged Cells
- How to Disable Copy and Paste in Excel without Macros
- How to Copy Merged and Filtered Cells in Excel
- How to Fill Across Worksheets in Excel
- How to Copy and Paste a Column in Excel
