If Value Exists in Column Then Copy Another Cell in Excel (3 Ways)

There are 3 ways to copy another cell if a value exists in a column in Excel. This article will walk you through each of them.

Suppose we have the dataset below containing the Code numbers of some Products with their Colors, Sizes, and Prices. We’ll check whether a Code exists in the column, and if it exists copy the Price of that Product.

Method 1 – Using the Filter Command

We can use the Excel Filter to search for the value.

Steps:

Select the Header of the Code column.
Go to the Home tab and select the Filter Option from the Sort & Filter drop-down.

As a result, a filter icon appears on each header of the data table.

Click on the filter arrow of the Code column to open the context menu.
Under the Text Filters menu, select only the cell that you want to find. Alternatively, search for that item in the Search box under the Text Filter menu.

Only the matching row is now visible; the other rows are hidden.

Press Ctrl+C to copy the Price.

Method 2 – Using the FILTER Function

We can use the FILTER function to do the same thing.

Steps:

Insert the value for which the function will search in cell I5.

Insert the following formula in cell H6:

=FILTER(B5:F20,B5:B20=I3)

Formula Breakdown:

Syntax of the FILTER Function:

=FILTER (array, include, [if_empty])

Array – B5:F20: The range in which the FILTER function will search.
Include – B5:B20=I3: The criteria. If met, the FILTER function will fetch the whole row.

Method 3 – Using VBA Code

Alternatively, we can use VBA code to do the same thing.

Steps:

Go to the Developer tab and select the Visual Basic option.

Press ALT + F11 to open the “Microsoft Visual Basic for Applications” window if you don’t have the Developer tab enabled.

A window named “Microsoft Visual Basic for Applications” will appear.

Click on “Insert” and select the “Module’” option from the menu.

A new “Module” window will appear.

Copy this VBA code and paste it into the Module window;

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()

Sets the Sub name for the VBA code.

Dim copyrng As Range 
Dim cell As Range 
Dim PasteRng As Range

Specifies the variables that will be used in the code.

Set copyrng = Sheet1.Range("B5:B20")

Sets up the cell range where we will search for a specific cell value.

For Each cell In copyrng
Set PasteRng = Sheet4.Range("A5")

Sets the output cells.

If cell = "C_O_M" Then Range(cell.End(xlToLeft), cell.End(xlToRight)).Copy PasteRng

If any cell in the range B5:B20 matches with the value “C_O_M”, then copy the full row.

Next cell
End Sub

Ends the For loop and the Sub.

To run the code, go to the top menu, click the Run option, and select Run Sub/UserForm. Or simply press F5.

The cell range B5:F5 is filled with values that are copies of the selected dataset.

How to Lookup a Value in a Column and Return a Value from Another Column in Excel

Suppose we want to lookup the product code in cell I5 and return the corresponding price.

Steps:

Use the following formula in cell I6:

=VLOOKUP(I5,B5:F20,5,FALSE)

Formula Breakdown:

Syntax of the 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: The range of the dataset within which the function will work.
Col_index_num = 5: The column index number of the column from which we will extract the result.
Range_lookup = FALSE: Determines whether the result will be an exact or partial match. FALSE is for an exact match and TRUE for a partial match.