Partial Word Match

rowan853

New Member
Joined
Dec 27, 2010
Messages
10
Any help would be greatly appreciated.

I want to type a sentence (multiple words) into cell A1 (tab 1) and if any of the words match a list of words in A:B (tab 2 - Ref. Cert. Tab) returns the value from column B (tab 2 - Ref. Cert. Tab)

I have managed to achieve this in part with partial matching but this only works if there is one word in cell A1 but i want to be able to type a sentence into cell A1

=VLOOKUP("*"&A1&"*",'Ref. Cert. Tab'!A:B,2,FALSE)
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).
 
Upvote 0
Hi,

Please use this document as an example.

I want to be able to type a phrase or multiple words into Cell C6 (Description) of the List Tab

The formula would then check the Ref Cert Tab and if any word (partial match) matched any of the words in A:A - Ref Cert Tab it would return the value of cell B - Ref Cert Tab into E6 - List Tab

My formula will return a match if I am to put “air” but not if I were to put “airS” or “hydrogen air”
 
Upvote 0
I think that you would need a macro to do what you want.
My formula will return a match if I am to put “air” but not if I were to put “airS” or “hydrogen air”
If I understand you correctly, this would pose a problem. For example, if you enter the word "airS" and expect a partial match, words like "prairie", "fair" and "airplane" would would meet the requirement. I'm not sure if this is what you want. To get a match for "hydrogen air" or any other phrase where the exact word to match is separated from the other words with a space, would not be a problem.
 
Upvote 0
Hi,

Please use this document as an example.

I want to be able to type a phrase or multiple words into Cell C6 (Description) of the List Tab

The formula would then check the Ref Cert Tab and if any word (partial match) matched any of the words in A:A - Ref Cert Tab it would return the value of cell B - Ref Cert Tab into E6 - List Tab

My formula will return a match if I am to put “air” but not if I were to put “airS” or “hydrogen air”
You might wanna google regex vba and see if it's what you want.
 
Upvote 0
Try This:
Excel Formula:
=INDEX('Ref. Cert. Tab'!B:B,MATCH(TRUE,ISNUMBER(SEARCH('Ref. Cert. Tab'!A:A,A1)),0))
 
Upvote 0
Solution

Forum statistics

Threads
1,213,537
Messages
6,114,216
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top