I have a large dataset in which I need to find a list of partial string
matches, and then return a value associated with each unique partial
string match.

Example
======

The dataset:

indallastexas
musicfan
bluechair
twentyminutesago

The lookup table:
LookupValue MasterValue
------------ ------------
dallas city
music noun
blue color
minutes time

Desired result:
indallastexas city
musicfan noun
bluechair color
twentyminutesago time

So for all occurences of the word "dallas", I need to return the value
"city" in that row. My dataset is quite large, and I need to repeat
this periodically so simply finding/replacing is not an option. I also
cannot parse the dataset in a way that will put all of my exact lookup
values into a column. There are no character patterns in the data that
vlookup can use, in other words everything is a string and the lookup
values in the strings do not have consistent placement patterns in the
cells.

This seems like a vlookup with a partial match vs. exact match. Any
ideas?


--
cdhmotes
------------------------------------------------------------------------
cdhmotes's Profile: http://www.excelforum.com/member.php...o&userid=29899
View this thread: http://www.excelforum.com/showthread...hreadid=496066

Hi!

Dataset in the range A1:A4

Lookup table in the range F1:G4

Formula in B1 entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(G$1:G$4,MATCH(TRUE,ISNUMBER(SEARCH(F$1:F$4, A1)),0))

Copy down as needed.

Biff

"cdhmotes" wrote in
message ...

I have a large dataset in which I need to find a list of partial string
matches, and then return a value associated with each unique partial
string match.

Example
======

The dataset:

indallastexas
musicfan
bluechair
twentyminutesago

The lookup table:
LookupValue MasterValue
------------ ------------
dallas city
music noun
blue color
minutes time

Desired result:
indallastexas city
musicfan noun
bluechair color
twentyminutesago time

So for all occurences of the word "dallas", I need to return the value
"city" in that row. My dataset is quite large, and I need to repeat
this periodically so simply finding/replacing is not an option. I also
cannot parse the dataset in a way that will put all of my exact lookup
values into a column. There are no character patterns in the data that
vlookup can use, in other words everything is a string and the lookup
values in the strings do not have consistent placement patterns in the
cells.

This seems like a vlookup with a partial match vs. exact match. Any
ideas?


--
cdhmotes
------------------------------------------------------------------------
cdhmotes's Profile:
http://www.excelforum.com/member.php...o&userid=29899
View this thread: http://www.excelforum.com/showthread...hreadid=496066

You are the king of Excel. There is none higher!


--
cdhmotes
------------------------------------------------------------------------
cdhmotes's Profile: http://www.excelforum.com/member.php...o&userid=29899
View this thread: http://www.excelforum.com/showthread...hreadid=496066

Is it possible to make the above formula for string match case sensitive
using the EXACT function?


--
cdhmotes
------------------------------------------------------------------------
cdhmotes's Profile: http://www.excelforum.com/member.php...o&userid=29899
View this thread: http://www.excelforum.com/showthread...hreadid=496066

Try replacing SEARCH with FIND.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"cdhmotes" wrote in
message ...

Is it possible to make the above formula for string match case sensitive
using the EXACT function?


--
cdhmotes
------------------------------------------------------------------------
cdhmotes's Profile:
http://www.excelforum.com/member.php...o&userid=29899
View this thread: http://www.excelforum.com/showthread...hreadid=496066