+ Reply to Thread
Results 1 to 5 of 5

Using VLOOKUP to replace a name with an ID from a certain column.

  1. #1
    Registered User
    Join Date
    10-22-2015
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    9

    Using VLOOKUP to replace a name with an ID from a certain column.

    Hi all,

    I've got a dataset with a column of names. I have another column with PLR_ID and a column with "NAME". I want to replace the names of the first column with the PLR_ID if the name is present in the column "NAME". If the name is not present i want the name of the first column to remain unchanged.

    EG I have "Mart Lieder" in the first column, i want to change this name to the PLR_ID if his name is active in the second name column. If his name is not present in the second column i want it remained unchanged.

    Can anyone tell me how i can do this?

    Hopefully i made myself clear, English isn't my first language

    Kind regards,
    Onkadonk

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Using VLOOKUP to replace a name with an ID from a certain column.

    Try this (untested)

    With names in column A, PLR_ID in column B and NAME in column C
    in column D
    =IF(ISNA(VLOOKUP(A1,B$1:B$1000,1,0)),A1,VLOOKUP(A1,B$1:B$1000,1,0))
    and copy down column D

    Now copy column D over column A
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    10-22-2015
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    9

    Re: Using VLOOKUP to replace a name with an ID from a certain column.

    Hi Special-K

    Thanks for your swift response! I tried your formula but it didn't change the names. Just to make myself clearer: Column E contains a name. Column N contains PLR_ID and column O contains another set of names. If a name in column O matches a name in column E, the name in column E should be changed in the PLR_ID from column N.

    Your formula did everything i wanted except change the name into PLR_ID.

    Hopefully you can help me out, i'm terribly grateful if you can help me get this thing to work!

    Kind regards,
    Onkadonk

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Using VLOOKUP to replace a name with an ID from a certain column.

    The secret to solving problems is making sure they're defined correctly.

    Try this
    =IF(ISNA(VLOOKUP(E1,O$1:O$1000,1,0)),E1,OFFSET($O$1,MATCH(E1,O$1:O$1000,0)-1,-1))

  5. #5
    Registered User
    Join Date
    10-22-2015
    Location
    Netherlands
    MS-Off Ver
    2016
    Posts
    9

    Re: Using VLOOKUP to replace a name with an ID from a certain column.

    Sir, you are a god amongst men. You've made me so happy i can't even begin to describe!

    Thank you for your time and effort, this forum is gold!

    Kind regards,
    Onkadonk

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Replace data in one column automatically based on another column
    By jzacharias in forum Excel General
    Replies: 1
    Last Post: 06-22-2015, 01:30 PM
  2. [SOLVED] Replace cell in Column A with value in Column B if contains similar character string
    By oybombadil in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-17-2015, 08:49 AM
  3. replace vlookup with vba
    By SSJAMES18 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-16-2015, 09:44 AM
  4. Replies: 3
    Last Post: 11-25-2014, 06:08 AM
  5. Replies: 3
    Last Post: 07-10-2014, 10:56 AM
  6. Replies: 4
    Last Post: 12-16-2013, 06:29 PM
  7. How to vlookup a Column contain special character and replace it?
    By miraclesuki in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-22-2013, 05:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1