Find and Replace Value based on Info

[Art14501]

Hi there,

I need a formula that will find duplicate values from Column A and in Column C, then replace the cell value of Column D with the cell values of Column B. For example:

Col A Col B Col C Col D
AAA 0 BBB 15
BBB 5 LLLL 5
CCC 8 FFFF 0
DDD 25 AAA 3
EEE 9 RRR 1
FFF 18 VVV 5
GGG 6 JJJ 9

I was looking at using VLOOKUP and REPLACE or SUBSTITUE, but then I read some articles that MATCH or INDEX was better than VLOOKUP. This is what I come up so far:

=((VLOOKUP(A2,$C$2:$C$200,3,FALSE)

as you can see I get stuck at the replacing value cell. I greatly appreciate any help or advice!

 

[Marcílio_Lobão]

Art14501, Good afternoon.

Only with formulas can not do what you need.
You need a MACRO in VBA EXCEL for this task.

I'm sure someone will help you.

Have a nice weekend.

 

[BQardi]

If I understand you correctly, you f.ex. want cell D5 (value 3) to be replaced with the value 0 (from B2), this is from the lookup value "AAA" in A2.
If this is the correct interpretation of what you want, then you can simply delete all values in column D and insert this formula in D2 and drag down:

=IFERROR(VLOOKUP(C2,A:B,2,FALSE),"No match")

If it's the other way round, inserting values from D column to B column, put this in B2 and drag down:

=IFERROR(VLOOKUP(A2,C:D,2,FALSE),"No match")

The IFERROR / "No match" -part, is simply the value that shows if no match is found (ie. if f.ex. the value "LLLL" connot be found in the lookup column), change "No match" to whatever you want.

 

[Art14501]

Hi there,

Thanks for the responses!

@BQardi: your formula was spot on, it's what I was looking for.

 

[BQardi]

You're welcome