# Combine VLOOKUP and SUBSTITUTE Functions?

1. ## 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. ## 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. ## 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!!

4. ## 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.

5. ## Re: Combine VLOOKUP and SUBSTITUTE Functions?

Originally Posted by Jonmo1
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

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

#### 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