# Vlookup with wildcards not working? Search cell for partial text match and return value

1. ## Vlookup with wildcards not working? Search cell for partial text match and return value

Hi,

In the enclosed sheet am trying to use the formula to search a number of entries that contain data and return a value if a partial match is found from a reference data set with a specific value. I have seen others use wildcards to search inside a string, but it does not seem to work for me. Perhaps a better way to do it?

=VLOOKUP("*"&A2&"*",\$A\$9:\$B\$13,2,FALSE)

If I just use the data I am looking for it works fine, just not along with other text.

Notes -
The notation I am searching for will always be at the end of the string surrounded by (). Not sure if that makes is better or worse....

Thanks in advance for any/all help.

2. ## Re: Vlookup with wildcards not working? Search cell for partial text match and return val

Hi,

That won't work because you actually need the wildcards applied to the lookup table values, not the lookup value. You might try this
=INDEX(\$B\$9:\$B\$13,MATCH(1,INDEX(COUNTIF(A2,"*"&\$A\$9:\$A\$13&"*"),),0))

3. ## Re: Vlookup with wildcards not working? Search cell for partial text match and return val

OR,

Try the following:

In C1:

=VLOOKUP(MID(A2,SEARCH("(",A2)+1,3),\$A\$9:\$B\$13,2,0)

4. ## Re: Vlookup with wildcards not working? Search cell for partial text match and return val

@xlnitwit

Ah! That seemed to work with my test data. I will apply to and report back.

Thanks you so much!

5. ## Re: Vlookup with wildcards not working? Search cell for partial text match and return val

Originally Posted by cbatrody
OR,

Try the following:

In C1:

=VLOOKUP(MID(A2,SEARCH("(",A2)+1,3),\$A\$9:\$B\$13,2,0)
Thank you sir - I will play with this one as well.

6. ## Re: Vlookup with wildcards not working? Search cell for partial text match and return val

hiii

use this simple formulla well working. i have tested.

=VLOOKUP(MID(A2,FIND("(",A2)+1,3),\$A\$9:\$B\$13,2,FALSE)

if yes plz mark this as Solved

Try this
Formula:

8. ## Re: Vlookup with wildcards not working? Search cell for partial text match and return val

Thank you all for helping.... one wrinkle that makes most of these not work is that my data (AA1) can sometimes be only two characters in length causing those to fail, i.e. (X3). The values inside the () is unique if that helps.

I could not get the index/match function to work with my data.

Thanks again - I will keep trying.

Formula:

Or
Formula:

10. ## Re: Vlookup with wildcards not working? Search cell for partial text match and return val

Originally Posted by AlKey
Formula:

Or
Formula:
@ alkey

Thank you - so that seems to be working! It is basically looking for any value within the () correct. 50 is the amount of characters to look within?

Thanks to all who responded and helped!

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