Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Partial String Match Using VLOOKUP

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


Post your answer or comment

comments powered by Disqus
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?

After reading another Q&A on using VLOOKUP to find partial matches, I have an
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.

After reading another Q&A on using VLOOKUP to find partial matches, I have an
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.

Greetings,

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

I am trying to find a partial string match within a range of cells and then I want to return the data in cells relating to the row that was returned.

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...

Hello,

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?

Greetings All!

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

Hi,

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

Hi,

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

vlookupmatchodd.xlsx

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

Hi All,
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.

I want to compare/match data from two 1,000+ row spreadsheets where the
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.

I am trying to use Vlookup to find a specific set of caracters to return the
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...

How to Use VLOOKUP or HLOOKUP to find an exact match

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 ...

How to Use VLOOKUP or HLOOKUP to find an exact match

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 ...

Dear Gurus . ...

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

I am trying to find for example "(3)" in a column of 50 rows where each row
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?

Hi,

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!

Hey Guys

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 Sub
This 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

Dear Gurus . ...

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

I need to use VLookup to find closest match for our Online Bedding company.

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!

Hi, this is my first post to this forum. I have a reasonable level of VBA skill, but this one escapes me. Hopefully some seasoned pro can help.

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!

Can anyone enlighten me with the following problem.

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 have a series of lines, each one identified by a keyword (or phrase) that is the first part of the string on that line. The keywords are not necessarily of the same length though.

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.


No luck finding an answer? You could always try Google.