+ Reply to Thread
Results 1 to 5 of 5

Combine VLOOKUP and SUBSTITUTE Functions?

  1. #1
    Forum Contributor
    Join Date
    12-29-2012
    Location
    usa
    MS-Off Ver
    Excel 2016
    Posts
    325

    Combine VLOOKUP and SUBSTITUTE Functions?

    Ok here's an excel sheet for an example:Book1.xlsx

    So I want a formula in column B to search column A for those listed words and if found, replace those words with what I have next to them in column B. So if found sfo in column A, replace with sf in column B. However, if it doesn't find any of those words listed, simply return what is in column A to column B.

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Combine VLOOKUP and SUBSTITUTE Functions?

    If you can split that list into 2 columns..
    I.E.
    Column G = sfo kan was sdg etc..
    Column H = sf kc wsh sd etc...

    Try this in B1 and Filled down.
    =IFERROR(VLOOKUP(A1,$G$1:$H$9,2,FALSE),A1)

  3. #3
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Combine VLOOKUP and SUBSTITUTE Functions?

    Hi,

    In cell B1 and copy down:

    =IFERROR(VLOOKUP(A1,$G$2:$H$9,2,0),A1)

    Though I notice that these are all either exact matches or not present at all, so there is no string substitution involved here.

    Regards

    EDIT: I forgot to say that I did precisely as Jonmo1 did first!!
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Combine VLOOKUP and SUBSTITUTE Functions?

    hi there. i would suggest you separate the text you have in column G into 2 columns. the text you want to find & the results you want. you can then do a formula like Jonmo suggested. otherwise, you can do this array formula:
    =IFERROR(INDEX(MID($G$2:$G$9,FIND("-",$G$2:$G$9)+2,255),MATCH(A1,LEFT($G$2:$G$9,FIND("-",$G$2:$G$9)-2),0)),A1)

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you might get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  5. #5
    Forum Contributor
    Join Date
    12-29-2012
    Location
    usa
    MS-Off Ver
    Excel 2016
    Posts
    325

    Re: Combine VLOOKUP and SUBSTITUTE Functions?

    Quote Originally Posted by Jonmo1 View Post
    If you can split that list into 2 columns..
    I.E.
    Column G = sfo kan was sdg etc..
    Column H = sf kc wsh sd etc...

    Try this in B1 and Filled down.
    =IFERROR(VLOOKUP(A1,$G$1:$H$9,2,FALSE),A1)
    Beautiful work once again haha.

    Need to work on my vlookup skills

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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