Partial String Match Using VLOOKUP

[cdhmotes]

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?

[Biff]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[cdhmotes]

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

[cdhmotes]

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

[Bob Phillips]

Try replacing SEARCH with FIND.

--

HTH

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


"cdhmotes" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>