CODEWITHSUNDEEP
excel
MelC
MelC

Reputation: 1

find matching cells from two columns and populate corresponding cells from third column to a fourth corresponding column

I am trying to update price change for over 800 items. Need to search for matching cells in ColumnA and ColumnC and copy corresponding row/cell from ColumnB to corresponding cell in ColumnD. ColumnC will have more than one cell with same value... I have tried several formulas, but come up with errors.

(Basically, if cell in A = cell in C then change A's corresponding row/cell in B to cell in C's corresponding D cell) hope that makes sense:)
Any help would be very much appreciated.

Example of file below

example

Upvotes: 0

Views: 46

Answers (1)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9966

You cannot have both the constant and the formula in the same cell. To update the column B values based on the column D values if the column A match is found in column C, you will need a VBA code to achieve the desired output.

Assuming row1 is the header row, please try this...

Sub UpdateTable()
Dim rng As Range, cell As Range, MatchingCell As Range
Dim n As Long, lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("A2:A" & lr)
For Each cell In rng
    Set MatchingCell = Range("C:C").Find(what:=cell.Value, LookIn:=xlValues)
    If Not MatchingCell Is Nothing Then
        cell.Offset(0, 1).Value = MatchingCell.Offset(0, 1).Value
    End If
    Set MatchingCell = Nothing
Next cell
End Sub

Upvotes: 0

Related Questions