Excel to replace cell value based on lookup in another sheet

Question

Cannot figure this out, I searched for hours and tested using vlookup.

I want to change or replace the value of cells in column A in sheet1 if value in column P is found in column A located in sheet2.

I get it close to what I want but I get blank cells when no match is found. I need the cells in column A to keep their original value if no match has been found.

Formula I used:

=IFERROR(VLOOKUP(P2,sheet2!$A$2:$B$13,2,FALSE),"")

Sheet1:

         A              P
 1 [References]---[ID numbers]
 2 [  000001  ]---[   123    ]
 3 [  000002  ]---[   456    ]
 4 [  newref  ]---[   999    ]
 5 [  000003  ]---[   789    ]

Sheet2:

         A              B
 1 [ID Numbers]---[References]
 2 [   123    ]---[xxx-000001]
 3 [   789    ]---[xxx-000003]
 4 [   456    ]---[xxx-000002]

Result sheet 1 should be:

         A              P
 1 [References]---[ID numbers]
 2 [xxx-000001]---[   123    ]
 3 [xxx-000002]---[   456    ]
 4 [  newref  ]---[   999    ]
 5 [xxx-000003]---[   789    ]

If this is not possible to achieve maybe using a prefix could work? Like adding (xxx-) when a match is found?

I appreciate your pointers, thanks for your advice.

Jameson the dog Jameson the dog · Accepted Answer · 2017-12-02T11:27:19

since you have to change the original value in Col A in Sheet1 to the formula, it can never "keep it's original value".

You'll have to add another column with the original values of A (it can be hidden, but it has to be there)

Let's assume Col Q holds the original values of A, in cell A2 the formula should be: =IFNA(VLOOKUP(P2,sheet2!$A$2:$B$13,2,FALSE),Q2) and assuming Q2 holds newref or A2 original value it should work for you

       A              P            Q
1 [References]---[ID numbers]---[original values]
2 [  000001  ]---[   123    ]---[hello]
3 [  000002  ]---[   456    ]---[world]
4 [  newref  ]---[   999    ]---[newref]
5 [  000003  ]---[   789    ]---[loremipsum]

BTW - if what you really want is either the value from sheet2 or the explicit string newref you could simply do =IFNA(VLOOKUP(P2,sheet2!$A$2:$B$13,2,FALSE),"newref")

also note I used IFNA instead of IFERROR for a more accurate result (for example #REF errors will be replaced to newref when using IFERROR

Excel to replace cell value based on lookup in another sheet

1 Answers