CODEWITHSUNDEEP
excelexcel-formula
Nuthatch92
Nuthatch92

Reputation: 91

How to replace a value only when a given value is present in another column in Excel

This question is a different version of another question I deleted because not correct. Sorry for those who answered the preceding question.

I have an Excel spreadsheet which contains more than 6000 rows filled with values. As an example, here are the first 4 rows of the spreadsheet:

A    B    C    D
1   1,1  1,11  7
2   1,2  1,22  6
3   1,3  1,33  8
4   1,4  1,44  2

What I need to do is to look for each of the value in column D (7, 6, 8, 2, etc.) in column A, and when one of this value is found I want Excel to replace the corresponding value in column B, with the value in column C that is in the same row of the value in column D that has just been found. So, the output I want to obtain looks like the following:

A    B    C    D
1   1,1  1,11  7
2   1,44 1,22  6
3   1,3  1,33  8
4   1,4  1,44  2

As you can see, in this case only the value present in B2 has been replaced with the one present in C2, and this because the only value that is present both in column A and in column D is "2".

Upvotes: 0

Views: 1610

Answers (2)

Naresh
Naresh

Reputation: 3034

Formula in E1

=IF(COUNTIF($D$1:D4,A1)>0,VLOOKUP(A1,$A$1:C4,3,FALSE),B1)

and copy down

enter image description here

New formula for E2 as per your question edit.

=IFERROR(INDEX($C$2:$C$5,MATCH(A2,$D$2:$D$5,0)),B2)

enter image description here

Upvotes: 1

SRA
SRA

Reputation: 219

You could try the below macro : 

Sub Macro1()

  Dim i As Integer
  Dim currentValue As Integer
  i = 1
    Range("D1").Select
    LR = Selection.End(xlDown).Row

For i = 1 To LR
    currentValue = Cells(i, 4).Value
    Range("A1:A" & LR).Select
    Set c = Selection.Find(currentValue, LookIn:=xlValues)
     If Not c Is Nothing Then
        Cells(c.Row, 2).Value = Cells(c.Row, 3).Value
     End If    
Next i

End Sub

Upvotes: 1

Related Questions