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

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

- Partial String Match Using VLOOKUP
- Partial String Match & Wild Cards Using VLOOKUP
- Partial String Match & Wild Cards Using VLOOKUP
- VSLookup with Partial String Match - Mapping between separate worksheets
- Finding Partial String Matches
- Using VLOOKUP to fill data with a partial string match
- Search multiple partial string matches in 2 lists
- Partial matching cells with locations
- Find A String Match
- Matching odd variables using VLOOKUP in conjuntion with Match and..
- Lookup Column Value Using a Partial string Match
- How do I match using partial or approximate values?
- Vlookup using a partial text match
- How to Use VLOOKUP or HLOOKUP to find an exact match
- How to Use VLOOKUP or HLOOKUP to find an exact match
- Tricky ... Pullout related rows that match a single word using Vlookup
- How do I use VLOOKUP to find part of string?
- Populate listbox using vlookup or index,match?
- Partial string match with file name
- Tricky ... Pullout related rows that match a single word using Vlookup
- Using VLookup to find closest match
- Select combobox item using string matching
- Wildcard search using vlookup
- MATCH on partial strings

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?

additional question. I have a dataset consisting of part numbers that follow

a basic structure, with differing portions. I need to find a list of partial

string matches, using wildcards with some of these part numbers.

===========================

EXAMPLE:

Data on Sheet1 (part numbers)

------------------------------------

B2-P-N49H-A0017

B2-N-N44B-A0014

MW3-11349H

MW3-11365AH

MWZ-6006

MWZ-6207

....etc...

Lookup Data

----------- -------------------------

A#### ARM ASSY

MW3 SHANK

MWZ CAP

===========================

For all occurences of "A" followed by a number ("A####", where #=actual

number, as above), I need to return "ARM ASSY". For all occurences of the

others ("MW3" & "MWZ"), I need to return their appropriate corresponding

values ("SHANK" & "CAP" respectively).

Thanks in advance.

additional question. I have a dataset consisting of part numbers that follow

a basic structure, with differing portions. I need to find a list of partial

string matches, using wildcards with some of these part numbers.

===========================

EXAMPLE:

Data on Sheet1 (part numbers)

------------------------------------

B2-P-N49H-A0017

B2-N-N44B-A0014

MW3-11349H

MW3-11365AH

MWZ-6006

MWZ-6207

....etc...

Lookup Data

----------- -------------------------

A#### ARM ASSY

MW3 SHANK

MWZ CAP

===========================

For all occurences of "A" followed by a number ("A####", where #=actual

number, as above), I need to return "ARM ASSY". For all occurences of the

others ("MW3" & "MWZ"), I need to return their appropriate corresponding

values ("SHANK" & "CAP" respectively).

Thanks in advance.

I am an Excel newbie (using Excel 2010) and am trying to establish ID mappings between two databases, where the unique linking identifier is the Product Title.

The Product Titles between these two data sources are not always consistent (semicolons, spaces, and variations in labeling exist).

Inconsistent Title variation examples:

a) "Aliens vs. Predators" or "Aliens versus Predators"

b) "Monk: Season One" or "Monk Season One"

c) "Halo 2" or "Halo Two"

I am currently using vlookup to populate ID mappings against the Product Title, but do not know how to do a partial string match rather than exact match by using range lookup FALSE.

How can I accommodate partial string matches for these mappings?

Any advice or help would be greatly appreciated!

Best regards,

Jamie

Here is the data:-

Name Dept Age Find Value Row Returned

Henry 501 28 Mary 4

Stan 201 19 enr 2

Mary 101 22

Larry 301 29

Row returned is the row number where a partial match is found using this formula - "=MATCH("*"&E2&"*",$A$1:$A$5,0)"

How do I then use this row number to find the other data in the row for the returned match (i.e. for Mary, department 101 and age 22)?

Please help as this is very frustrating...

Lets say we have a two column table that has column A as:

SKU

1000

1000-yellow

1000-white

2000

2000-yellow

2000-white

3000

3000-yellow

3000-white

and so on...

I need to fill column B with size in each and every row from another worksheet in the same workbook where I have two columns. I have been able to use VLOOKUP to do this. The table array would look like this in column A on the second worksheet:

SKU

1000

2000

3000

and so on...with column B having the size

10

20

30

It is a simple VLOOKUP for matching SKU's 1000, 2000, 3000 etc because it is an exact match but how do I do a partial text match to match the first part of the cell value or even search the cells for the matching partial string and place the proper value 10 for size for SKU's 1000, 1000-yellow, and 1000-white, what would be value 20 for 2000, 2000-yellow, and 2000-white and so on?

I am a statistician who uses Excel quite a bit for data management. I have found this forum very useful on occasional visits, but this is my first post, and i would appreciate some help. Please excuse me if the question is a repeat, but I couldn't find an exact answer so far.

I have 2 lists of food items: one is a short list of Commodities (food items) from an official price index (Column A in example below) and the other is a longer list of agricultural data with names of Crops (Column B in example below)

matchexample.jpg

Some of the Crop names in column B will be partial/complete) matches for the Commodity names in column A. Thee are about 100 names in col. A & 300 in col. B, and I need to get a list of all the matching pairs, e.g.,

matchexample2.jpg

(I will upload the example file later, as I have reached the limit this time, but I suppose the pictures might be enough!) Look forward to the answers.

Thanks & regards

I tried a lot to find an answer to this but this seems like a tricky probleum.

I have a set of keywords which have locations( something like "jobs in London") in them in column B and set of all locations in the UK (London, Manchester, South East etc) in Column A.

All I want to do is find all the keywords in column B that may have any of the Location terms in Column A. IF I do a partial string match with "Vlookup" it just just shows me the first instance of the location keyword.

It will be good if I can find a formula or macro that will highlight all the keywords in Column B which have any of the locations in Column A.

Thanks in advance for your help

I have a list of ~20000 numbers that have a dash and a self check digit appended to the end. I am trying to find a partial string match from another list of numbers (~2000) that do not have the dash and self check digit appended.

Column A has the full list of numbers with the self check digit. The format is 50008-2.

Column B has the list of numbers that I would like to match so I can get the self check digit. The format is 50008 (5 characters, no dash or self check digit).

I would like to match the numbers from the rows in Column B and find the corresponding match in Column A (5 digit match) and then put the value from Column A into Column C.

I appreciate any help you can provide.

Thanks, Ken

I've been trying to use VLOOKUP in conjuntion with Match using odd variables that dont match the original variable, but am failing to add the correct string of functions as in attachments

Can anyone help?

I am trying to do a VLookup matching multiple values.

The lines I'm looking at look like this:

Job No Date of Payment Received Amount Received

11058 21.10.2010 £1000

11568 06.06.2008 £586.59

11623 13.4.2011 £499.95

11623/1 25.9.2010 £258.98

11623/2 30.11.2010 £759.33

11623/3 01.02.2011 £435.25

11899 25.6.2011 £799.95

When the Accounts person receives a new payment from each job number, she records them as above. If there are more than one payment (as is 11623, she enters them as 11623/1, 11623/2, 11623/3 - indicating 3 separate payments).

How do I use a Vlookup to:

a) Get the latest payment received date for each job number

b) Get a sum of what's been paid to date (regardless of whether there is one job number e.g. 11058 or 3 eg 11623/1 /2 & /3?)

Is this possible? Any help gratefully received.

values are similar but not exactly alike. For example, on one spreadsheet I

have a name like John P. Smith Associates, and on the other spreadsheet I

have John Smith Assoc. I would like to find and return a match using a

partial value, such as "Smith Assoc" ...sort of using a wildcard containig

the value. I've tried using vlookup for exact and approx matches but it did

not work out for me.

match to that set. The problem is I am trying to pull the set from a

description.

Example:

Lookup value:

ZZWTH XXX SSKLDF

Array has values in cells as follows

Col 1 COL 2

WTH Wheat Home Feilds Farms

I need Vlookup to scan the lookup value for the WTH (that is never int he

same place in the lookup value) and match that to col1 and return col2 not

sure if this is possible but it would be nice to figure this one out...

The VLOOKUP and HLOOKUP functions contain an argument called range_lookup that allows you ... matching value should be returned. range_lookup It is a logical value that ...

The VLOOKUP and HLOOKUP functions contain an argument called range_lookup that allows you to find an exact match to your lookup value without sorting the lookup table. Note It is ...

I have a worksheet with 5 columns and 400 rows. All contents are

sentences.

I use Vlookup but it only can show result that matches the input

exactly.

How to ask vlookup to retrieve all the sentences that match my single

input?

And how to ask vlookup shows multiple searching result? (It stops after

getting the first from top)

Please help. Thanks in advance.

YJL

--

YJL

------------------------------------------------------------------------

YJL's Profile: http://www.excelforum.com/member.php...o&userid=28693

View this thread: http://www.excelforum.com/showthread...hreadid=484200

has a name in it. The name for example may be "Jones" in row 1 and "Smith

(3)" in row 8. The "(3)" means something to me in the database. I need to

seperate each name that is marked by the "(3)" and utilize the information in

the next column that corresponds with the "(3)". How do I use VLOOKUP to

find part of this string?

Am just wondering if its possible to populate listbox by vlookup or index, match functions.

I have 2 listboxes , and I select an item in listbox 1

when this is selected - i want the corresponding items to populate listbox 2

is it possible to use vlookup or index to do this?

can anyone suggest a macro /code to do this, if it's possible ?

thanks!

I have this code:

Sub Search_My_Files() Dim MyFile As String Dim str As String str = InputBox("File Name") MyPath = "C:" MyFile = Dir(MyPath) Do While MyFile <> "" If MyFile Like "str.xls" Then Workbooks.Open MyPath & MyFile End If MyFile = Dir Loop If MyFile Like "" Then MsgBox ("No Match found") End If End SubThis matches the string with the whole file name, but what I want is a partial match so, for example, if the user enters "123" and the file name is "123 abc" then it will see that file as a match and open it. Does anybody know how this is done?

Thanks in advance.

Jamer

I have a worksheet with 5 columns and 400 rows. All contents are sentences.

I use Vlookup but it only can show result that matches the input exactly.

How to ask vlookup to retrieve all the sentences that match my single input?

And how to ask vlookup shows multiple searching result? (It stops after getting the first from top)

Please help. Thanks in advance.

YJL

Example -

Table

Short Name............................Weight

Twin Comforter..........................13

Full Comforter............................15

Queen Comforter........................17

King Comforter...........................21

Excel Data Sheet.....................Weight

Green Twin Comforter..................*Need it to auto insert 13

Full Comforter (Green)..................* Need to to auto insert 15

Any help would be great, Thank you!

I have a 2-column combobox on a UserForm, which is populated from a named range in another worksheet. When the UserForm is opened, I want the selected item in the combobox to be based on a string match with a value taken from a textbox in the worksheet, with the string matched to the second column in the combobox.

So for example, if the values in my combobox are:

1, Apple

2, Pear

3, Orange

and the value from my textbox is "Orange", I would like to have row 3 selected in my combobox when the form opens.

I can do this for a one-column combobox using Application.Match and Application.Transpose(ComboBox1.List) (see the code snippet below), but can't get this to work when searching on the second column of the combobox (runtime errors and type mismatches galore).

With Me.ComboBox1 x = Application.Match(Worksheets(1).TextBox1.Value, Application.Transpose(.List), 0) If Not IsError(x) Then .ListIndex = x - 1 End With

I'd really appreciate any help that anyone can provide - this has me tearing my hair out!

Task: To sort on sales item number from item + version number list

Problem: No uniform string length (cannot apply LEFT or RIGHT)

Cannot use vlookup : argument FALSE gives only exact match, TRUE gives the next highest value in the target list.

Manually using (ctrlF) with sales item* wildcard works perfectly. Is there a way to incorporate this manual method into a macro to search line for line in a list and to output the results in a new worksheet?

I'd like to hold the keywords in a list and identify each line according to which keyword the leftmost characters of the line match. Ideally, something like the MATCH function would do this, but it only matches strings of a fixed length.

For example, let's say I have a line like:

TEXT created by Blah-de-blah on 27/05/2008 containing blah-de-blah-de-blah

My list of keywords might be:

ACCESS

BASIC

CSV

DATABASE

JAVASCRIPT

PROJECT

RICH TEXT

SPREADSHEET

TEXT

WORD

At the moment I am able to do a MATCH using the first three characters of the line with the first three characters of each list element. That works, but I'd like to build something more reliable and arbitrary for the day when list elements cannot be uniquely identified by the first three characters.

Does anyone have any recommendations?

Thanks in advance.