Partial String Vlookup

Nov 25, 2008

I have two columns Cust. Phone and State. The Cust. Phone column has the full 10 digit number. Some with the preceeding 1 and some without. The vlookup will sit in the State column. I am trying to perform a lookup using the area code.

I have tried the following but always recieve #N/A.

=IF(LEFT(CELL("contents",A2))="1",VLOOKUP(MID(A2,2,3),D2:E8,2,FALSE),VLOOKUP(LEFT(CELL("contents",A2),3),D2:E8,2,FALSE))

It makes sense if I walk through it but fails to work.

I am trying to use the HTML maker to upload an example but I'm running into problems. I'll update ASAP.

ADVERTISEMENT

VLookup - How To Find Partial Match Within Larger String

Apr 9, 2014

I'm trying to lookup a 10 digit number against a string of numbers seperated by commas. And then return the Carrier Name and On-Time

LOOKUP TABLE
Order #(s) Carrier Name On-Time
5082940535,5082940507 Freight Lines Yes
5083055781,5083056150,5083056098 Ocean X No

Order # Carrier Name On-Time
5082940535 ? ?
5083056150 ? ?
5082940507 ? ?
5083056098 ? ?

View 5 Replies View Related

Using VLOOKUP To Fill Data With A Partial String Match Separated With A Dash

Aug 18, 2014

I have a list of items in Column A and size info on Column B i want to being in the info to another sheet with a vlookup but the problem is that I have a lot of items with different colors so after the item no. it has a dash and a letter or two for the color so i want to bring into my new sheet all info.

I have in my old sheet for all items regardless of color so for instance in my old sheet i will BR1000-EM and then in my new sheet i will have BR1000-R and BR1000-SA and BR1000-YC how do i make a vlookup it should only lookup the values in both sheets only till the dash (i cant use a certain no. like left,6 because the item no can have more then 6 but it always has a dash when it has a color code) also not all items have dashes so the dash is not always there but when its there i would like that the lookup should stop by the dash.

View 6 Replies View Related

VBA Find Partial String In Array And Output Found String Array Value?

Mar 31, 2014

I am trying to do a sort of index match thing using VBA. What I am attempting to do is to use the prefix of a long number and try to find that exact prefix in a string array, and output that string array value. So this is what I have so far as a test:

[Code].....

So I can match the text exactly so if I put PREFIXB in cell A1 in this example, i will get the msg box saying "YES", but if I make it PREFIXB1231k4j3jj1kj32cj, it will display "NO". I want to get it so that PREFIXB will be displayed in the cell that I put the formula in. So if A1 = "PREFIX1AAA100CF" and cell B1 = "=ABC(A1)", cell B1 will display "PREFIX1AAA".

Now the thing is that these prefixes can have different lengths, but will never encompass the exact prefix of another. So if I had a prefix of: PRE1AB, I won't have a prefix of PRE1A.

View 2 Replies View Related

Select Based On Partial String Then Delete Based On Full String?

Jun 13, 2013

Working with a scheduling report and trying ultimately to get it down to a line item report with the associate information and their earliest start and latest end for the week. Already made a lot of fixes to the formatting involving junking empty cells and trash data, but running into a road block on some of the trash data.

Here is the format of the report at this point. Names have been changed to protect the innocent.

Agent: 2366 Bacon, Kevin

Date
Start
End

[Code].....

So what I need is something that will iterate through the report...such as an while and find the cells in column A that start with "Agent:" It needs to compare that cell to a stored value to see if this "Agent:" is a repeat. If it is then it needs to delete that row and the next one and shift up. If it is not then it overwrites this "Agent:" with the previous one in the stored value and continues the while.

I know what I want to do...but not shure on the code cause I am not great at VBA.

Oh...if it matters probably need to allow for about 10k line items to iterate through...right now report is at 6k...but allow room to grow.

View 6 Replies View Related

Partial String Lookup

Sep 5, 2007

I need to do a lookup in a column for part of a string and return the adjacent value in a different column. Works fine using VLOOKUP as seen in the following example except that the return value is being truncated.

Lookup range in column A:
A001,A002
A003,A004
A005,A006

I need to find A001 and return the value in column I of the same row. I am currently using {VLOOKUP("A001", LEFT(A1:I500, 4), 9, 0)} This does work entered as an array formula but it is only returning the left 4 characters of the column I value. I need to lookup the left 4 characters in the lookup range but return all the characters in the return range.

View 2 Replies View Related

VBA Partial String - Put First 4 Characters Of One Cell Into Another

Jan 30, 2013

I haven't had the need to work with partial strings till now and having difficulty finding the right context in other threads. I need to put the first 4 characters of one cell into another cell. The line in the below code with the comment is the one I need. It's the only one where I need only part of what is in the cell.

It should be = the first four characters of cells(zRow, "A")

Code:
Dim LastRow As Long
Dim zRow As Long
Dim cRow as Long

LastRow = Sheets("Datasheet").Range("N65536").End(xlUp).Row
zRow = 1
cRow = 2

[Code] ......

View 1 Replies View Related

VBA Partial String Extraction From Variable?

Feb 12, 2013

I have a comment that I'm putting into a variable for the purpose of obtaining the persons name.

Cell C3 (in this example) just contains their employee number, but it's comment contains...

Tech ID: 123456
Name: John Doe

The Tech ID is variable from 3 chars to 6 chars and obviously the name is size is also variable. The end result will be a variable with just the persons first and last name extracted from the comment.

Code:
Dim mycmt As String
Dim techname As String
mycmt = Sheets("tech").Cells(3, "C").Comment.Text
techname = Right(mycmt, BIG-OLE-BALL-OF-CONFUSION)
MsgBox techname

My 2nd question. This is the 3rd time in recent days that I've needed a partial string and Mid, Left, Right, Len, Find just boggles my mind.

View 6 Replies View Related

Macro For Partial String In Text

Oct 21, 2008

I want to write an macro where it searches for text in a column, but the text may be a partial string. The text is in column B, with account numbers in column A, like this:

Column A Column B
1100 Jay
1101 Jack
1102 Jackson
1103 Jacksony
1104 Jefferson

For example, I want to search on Jack in column B. In the above example, I want it to find accounts 1101, 1102, and 1103, and to copy that information to another spreadsheet, say, sheet2.

If possible, I'd like it to copy the first account number and name, 1101 Jack, and if that's not what the user wants, they click next and they see 102 Jackson and so forth.

View 9 Replies View Related

Formula To Match Partial Text String?

Jun 3, 2014

I'm working on a formula to make it enable a part of the text then return the best possible match. Below is my formula

=MATCH("*"&$A11&"*",'[Customer Master List - 05.30.xlsx]Export Worksheet'!$B$82:$B$1298,0)

However, it works with some text but won't work for some.

For example, I have this text CARE-A-LOT, INC and in the master sheet there is a similar text like this CARE-A-LOT. I want it to return CARE-A-LOT as this the best match possible.

View 2 Replies View Related

VBA Function To Search Partial String And Return Value?

Jan 28, 2014

I'm trying to come up with a VBA function that would return cell values based on another cell value.

I can do this by comparing a range (column of cells) with the cell value to match and then return the value of the cell next to it.

For instance,

The function should search a column for partial text, as follows:

TS ID
PDT ID

TS 1.1
PDT 1

TS 1.2
PDT 2

TS 1.3
PDT 3

TS 2.1
PDT 4

TS 2.2
PDT 5

TS 3.1
PDT 6

TS 3.2
PDT 7

In the above table, the function should for partial text and return string as follows:

TS covered
PDTs

TS 1.1, TS 1.2, TS 1.3
PDT 1, PDT 2, PDT 3

TS 2.1, TS 2.2
PDT 4, PDT 5

TS 3.1, TS 3.2
PDT 6, PDT 7

So basically I am searching for partial text TS 1. and so on

View 4 Replies View Related

Get Partial String From Active WorkBook.Path

Sep 30, 2009

I tend to stumble when trying to pull certain characters from strings, not sure how to stop and start my search.

View 4 Replies View Related

Delete Row Based On Partial String Criteria

Dec 10, 2009

I have 2 columns A and B. The data is in column B.

What I'm trying to do is delete entire rows from my current selection if they do not begin with mailto:

View 9 Replies View Related

Search Partial String, Return Value In Next Column, Vba

Feb 13, 2008

I would like to search cells in column D for the partial string, "PIPE," (A full string may look like this: 'PIPE, 24"ODx0.375"WT API-5LX-65,ERW OR SMLS'). Then, if it's there, return the value "LF" in the corresponding cell in column C. If that string isn't found, then I'd like it to return "EA".

I know this seems pretty easy, but there's a small problem. The word "BENT PIPE," could be in Column D, in which case, I would want it to return "EA" instead of "LF".

View 3 Replies View Related

Retrieve Record On Partial String Search

Feb 20, 2008

I have created a Userform for entry data's in a Excel2003 file. I would like to retrieve a complete record (= a row) by searching on a string contained in a cell of this searched record-row. Here an example of a row and I'm searching upon "2041" via an inputbox.

idn° claims Dateréf. Suppl supplier Fax n° PO
1AAA2041 2/01/08200039 BOSCH (RAS) 056/20.26.75 774634

View 9 Replies View Related

Sort Pivot Table Based On Partial String...

Oct 6, 2008

I've got a group of data within a pivot table that I want to be sorted based on only part of the string. For example,

Standard Kitchen
Standard Master Bath
Standard Hall Bath
Optional Kitchen
Optional Basement Bath

First they would be sorted by whether they begin with Standard or Optional. Then within those groups, they would be sorted whether they contain Kitchen anywhere in the string. So that the order would be Standard Kitchens, Standard Anything Else, Optional Kitchens, Optional Anything Else.

View 3 Replies View Related

Partial String Check In Cell Against Range For Return

Feb 15, 2010

I've posted this query before, not on this forum, but I don't think the replies I've had so far are going to do what I want. Initially I was looking for a formula, but the suggested pile of nested IFs won't work for the number of conditions. I saw a previous post on here for a VBA macro to search for a text value in a cell against the cell contents of a range and it seemed to do at least the first part of what I wanted. I attempted to manipulate it a little to test its applicability for my own nefarious purposes but for the life of me I can't get it working.

This is complicated by the fact that the actual data is commercially confidential, so I can't show you the actual file, but I can fake what I want with two simpler ones. I've attached them to this post. What I want is a fair bit more complex than the other post I found - I want to be able to compare a partial text string from a given cell in a range ('Check Value' in the attached TestBook2 ) against the strings in a range of cells ('Value 1' in TestBook1), and return the corresponding value from 'Test Value' to the corresponding adjacent cell to the tested 'Check Value', with an order of precedence, for example...

Testbook2 contains an entry in C5 of 'a, e, h, z, x, y'. Testbook1 shows that the return for a, b, c, or d is 'moo', for e, f, or g is 'steve' and for g through q is 'fred', all others being no returned value. Moo>steve>fred, so I want the corresponding 'moo, steve. fred or <blank>' cell to contain 'moo'. Conversely, C6 contains 't, u, z' and therefore shouldn't have a value in 'moo, steve, fred or <blank>'. C12 contains 'f, z, s, y, u' and C15 'i, x, z, s', and therefore should display 'steve' and 'fred' respectively.

View 3 Replies View Related

Summarize Data Based On Partial String Matches

Jun 30, 2006

I have two questions:

a. Check the code below:

Dim news1 As String, news2 As String

news1 = "new"
news2 = "polygon"
Dim countie As Integer
For buddie = 2 To b1.UsedRange.Rows.Count
'If b1. Cells(buddie, 1).Value Like "new*polygon" Then
If b1.Cells(buddie, 1).Value Like news1*news2 Then
countie = countie + 1
n1.Cells(buddie, 10).Value = "test"
End If
Next buddie

I have 2 strings, news1 and news2, i need to use the like function to check the occurance of these two strings in all the cells, and just for testing purpose, im printing 'test' it out in another excel sheet.

How do i make that work? the commented line shows what exactly i want, only that i dont want to hard code the string values.

b. If i have a value in the cell:
/new/blahblah/anycra/polygon
how do i split it so as to assign two variables to have 'new' and 'polygon'.

View 9 Replies View Related

Lookup Partial String To Return Data From Corresponding Column

Mar 22, 2007

I have a simple lists containing two columns. One column contains a five digit number and the other a vendor name. The vendor name in most cases is two to four words. I am wanting to type in a partial string of the vendor name and it return to me the 5 digit 'vendor' number.

Col A Col B
20567 3M Electrical, Inc.

I want to type in '3m' or '3M' or 'electrical' or 'ELECTRI' and it return the 20567. The other part of this is that there may be two rows with the same info in which I would need to see both...

Col A Col B
20567 3M Electrical, Inc.
21789 3M Tape Division

Is this possible with standard lookup features in Excel or does someone have a VbScript or macro that will accomplish this?

View 9 Replies View Related

Partial Vlookup Match

Nov 13, 2009

In cells A1:A10 I have the following list;

FLOUR
DOUGH
CHICKEN
BEEF
PEPPERS
TOMATOES
CHEESE
ONIONS
POTATOES
SALAMI

I want to be able to get a partial match that will allow for spelling mistakes by the user. The wild card (*) seems to only look at the left-most text and return #N/A if the text isn't matched sequencially.

For example ....

View 12 Replies View Related

How To VLookup With Partial Match

Sep 21, 2002

How do you do a vlookup with a partial match?

View 5 Replies View Related

Vlookup Of Partial Matching Values

May 6, 2007

I have a worksheet with 2 tabs. Tab 1 has data in two columns, column A contains a part number (6 characters long) and column B contains a value for each part number.

Tab 2 is a sheet with multiple colums of data. I need tab 2 to look at the data in tab 1 and display the value of each part number in a specific column that has corresponding part numbers but are 15 characters in length. The part numbers in tab 1 are the last 6 characters of the part numbers in tab 2.

I have been trying to use vlookup in tab 2 but it won't work because the column of part numbers in tab 2 is a full part number and the part numbers in tab 1 are only a partial part number (last 6 characters of part number in tab 2).

Tab 1 is a worksheet that is created from a Access query of a sql database. I cannot control the abbreviated part number that the query generates.

Tab 2 part numbers (the full 15 character number) also is generated from another access query which I also can't control.

Is there any way I can have tab 2 sheet look at the data in tab 1 and display the values for the part numbers in tab 2.

View 9 Replies View Related

How To Apply Categories Based On Partial Text Match Like Vlookup

Jan 17, 2013

I do PPC work and you can export query data from Adwords with Column A being the actual search query. Then column B - E are impressions, clicks, cost & conversions (from each query). In this example, cell A1 might be running shoes, cell A2 might be walking shoes, cell A3 is running pants, and cell A4 is baseball pants. I want to add a new column (F) that does product category groupings based on partial text matches in column A. For example, anything that includes the text string shoe or shoes should be labeled as shoes in column F. Similarly, all queries that contain the characters pant would be listed as product category pants in column F.

Currently I do this manually by conditional formatting on column A for contains text and then color the cells. Then I sort by cell colors. Then I manually type in the product category into F for each color block in A. Needless to say, this is slow and manual.

What I would prefer is to have a master table (like a vlookup) on sheet 2 where column A is the list of partial text matches and column B is the product category to be returned if that the partial text in sheet 2 column A is found in the query list on sheet 1 column A. This way, on sheet 2 I could have cell A1 sneaker, cell A2 shoe, cell B1 shoes, cell B2 shoes, etc to manage the correlation between text strings and product category groupings.

Basically I want a formula to put in the cells on sheet 1 in column F that searches all of column A on sheet 1, looking for partial text matches from column A on sheet 2 and returning corresponding product category in column B on sheet 2.

View 6 Replies View Related

Partial Text Lookup In Text String And Return

Mar 15, 2007

Cell H1 has a variable string of references for eg
"FI570783AQ3516346EQ3516346FXVB123456"

I want to return the reference beginiing with "FXVA" so it would ignore the rest and only return FXVB123456 - this should always be 10 characters.

Just to add another complication to the mix, there may be 2 "FXVA" references in the string and i want to get both (these can be in the same string so FXVB123456FXEL123456 - but this might not happen regularly.

View 9 Replies View Related

Excel 2007 :: Match Partial Text When Partial Text Is Not Exact

May 20, 2014

This is for Excel 2007,I have two sets of model numbers. One set is the full model numbers of the units we use, and the other is an abbreviated form used to lookup up certificate numbers. I need a way to match these up so I can use one set of search criteria to find out if there is a match. Here is an example of what I need to match with a partial text match:

H,AE35(6,9)36+TD and AE3563636D145C2501AP
H,RE36(6,9)36 and RE36936C145B2505AP

if I could do this with a formula that matches multiple items at the same time (ie; if A & B & C match=true) with the above model number being one of those items (certificate numbers are issued for sets, but the other model numbers are fine).If that's not possible, a one time VBA run to match all of the abbreviations at once would also work. If these items are matched up with a one time VBA, the VBA needs to account for their being more than one match for each abbreviation depending on the size of the unit.

So H,AE35(6,9)36+TD could be matched to:
AE3563636D145C2501AP
AE3563636D175C2501AP
AE3563636D210C2501AP

View 11 Replies View Related

Use A String In VLookup?

Apr 8, 2014

I have a standard lookup table in Sheet1 with all the zipcodes and FIPS codes for the counties in 3 states:

Col-A.....Col-B.....Col-C
Zipcode..County..State
19316........029.....PA
19317........045.....PA

In Sheet2 I have data exported from a database from which I need to generate columns of county codes and states in a third worksheet.

The problem is, most of the zipcodes in Sheet2 are 9-digit and some are 5-digit:

Col-F
Zip
191493712
19133
191203510

I can extract the 5-digit zipcode using LEFT, but I can't get it to work in a Vlookup formula in a single formula.

I tried creating an intermediate column to display just the 5 digits, but when I reference that in a Vlookup formula I get N/A. I can copy that intermediate column, paste values, and convert to number and it works fine in the Vlookup (but the end user is never going to be able to do this).

How can I--in one formula--extract the first 5 digits from each cell and use that string in a VLookup formula to display the proper FIPS codes?

View 2 Replies View Related

VLOOKUP And SUM On Part Of String?

Apr 13, 2012

My sheet contains values like these:

A/5
A/3
B/4

What I want to do is sum all the values after the / for a particalur value before the /:

A 8
B 4

I already tried VLOOKUP, but this function wants you to point to a certain cell to return instead of a just a part of the cell. It should return 5 for the first row to be able to perform a sum for every value of A.

How can I perform such a calculation without having to copy the values to separate cells? Can I contain all of this in one function?

View 5 Replies View Related

VLOOKUP For Part Of A String

Oct 4, 2012

Im sure this cant be done because it defies the logic of the LOOKUP function but I know there are some clever people on here so i thought id ask!!

I have 2 workbooks containing similar data from 2 different sources; the one common data item in both is a clients address but have the age old problem of data quality, which needs sorting eventually but i need a quick fix!

In workbook one for example the address will be recorded as RUSKIN RD

In workbook two the address will be recorded as RUSKIN ROAD

A simple look up wont work obviously as the 2 values dont match, then i thought of using the LEFT function but again this goes against the logic of the VLOOKUP.

View 2 Replies View Related

VLOOKUP String Of Text

Nov 17, 2009

I am wanting to look for a string of text within the "lookup_value" that matches the text that's in the "table_array".

View 9 Replies View Related

VLOOKUP And STRING LEFT

Feb 12, 2010

I have a issue with VLOOKUP and LEFT STRING ETC I have a lookup table see below

AB10 Grampian
AB12 Grampian
AL8 Anglia West
AL9 Carlton LWT
B1 Central West
B10 Central West
B11 Central West
B13 Central West
B14 Central West
B15 Central West

View 9 Replies View Related

Vlookup Part Of String

Sep 29, 2006

I'm trying to use this formula to lookup the value in the array that matches, however, the first 4 numbers aren't known (thus why I'm doing the lookup) =VLOOKUP("*"&C2, 'KLIF Data'!A:C,1,0)

The * is being interpreted as a character, is there a way to perform the Vlookup function using a wildcard? Or just ignoring the first 4 characters of the cells in the array would work.

View 7 Replies View Related

Match Words To A String Using VLookup

May 10, 2014

Take a look at the attached workbook.

Tab 'Internal' is an example of our internal customers and how much they spent with us.
Tab 'External' is a list of companies in our area that we could do business with, it came from a marketing firm. Columns B:J contain my formulas. B is my lookup.

I need to match the two sheets together and pull the dollars spent from Internal to External. The problem is that the marketing firm did not use the same naming conventions that are in our systems. This means a simple Vlookup will not work.

Here is what I did so far: used VBA and formulas to break apart the company names in External, then using a modified Vlookup to match the words to the names in Internal. My accuracy rate is less than 1%.

View 1 Replies View Related

Vlookup To Search For Part Of A String?

Nov 17, 2008

If I have a store number (say 1234) and I am trying to search for something which contains 1234 (i.e. the whole string of ABCD1234, ABCD1235, ABCD1236 etc) is it possible to do this in one VLOOKUP formula?

One way of doing it would be to VLOOKUP("ABCD"&"1234",....) but I would rather do it the other way around, to prevent the formula from not working.

View 4 Replies View Related

Vlookup Formula With Date String

Sep 22, 2009

i have attached an example to illustrate the data source and result expected.

View 3 Replies View Related

VLookup Only Text String From A Cell

Nov 13, 2013

I do have a price list for my inventory for each product code (only letters)

E.g. productA 100
productB 200

When I'm writing in the order list, I'm writing in a single cell product code + size e.g. productA 5, productB 7

I need to get the corresponding price for each cell.

View 5 Replies View Related

Vlookup Part Of The Text String

Nov 17, 2006

Is it possible to lookup if at least 8 characters of a text string is found in another text string?

View 9 Replies View Related

VLOOKUP Part Of A Text String.

Nov 17, 2006

Is there any way I can Vlookup for just part of a text stiring in a cell.
I am trying to vlook if a part number contains the letters “HFR” if it does I want
It to refer to the comment I have made in the second column of my vlook table against HFR.

View 9 Replies View Related

#VALUE! Error Creating String For VLOOKUP

Apr 25, 2006

Needing to do a VLOOKUP on two criteria, I have set up a string using "&" to join together the contents of two cells. This is in a workbook with many identical worksheets. The string works for some of the sheets but in others, for reasons I simply cannot fathom, it is returning a #VALUE! error.

View 9 Replies View Related

Vlookup To Return Second Occurrence Of String

Mar 14, 2007

I have multiple automatically updating data sets and I need to extract certain data to perform calculations on, this is the format of the data

SPLIT AB H 2B 3B
HOME 20 6 1 0
AWAY 20 7 2 0
SPLIT R HR RBI
HOME 2 0 1
AWAY 3 1 4

basically, what I need is to extract the data for home and away, and put them each on one line, no problem if this is always the format, but there are other lines of data that I don't need thrown in there at times which shift the data up or down
a simple vlookup will extract the first occurance of home, but I need another formula to extract the second occurance so I can have them all on one line and have the data as:

SPLIT AB H 2B 3B R HR RBI
HOME 20 6 1 0 2 0 1
AWAY 20 7 2 0 3 1 4

View 2 Replies View Related

VLOOKUP To Find Values In Text String?

Feb 17, 2010

I need to to highlight keywords within a block of text (string). I don't need to necessarily return a value with the VLOOKUP. I just need to compare keyword list in Column B to the Text in Cell A1. If the word from the list appears in the text, I need it to be highlighted for every instance.

EXAMPLE
Text String (A1)

Welcome to Jake's Car Accessories website. We have all of your car accessory needs including car covers, truck covers, custom car grills and other car accessories.

Keyword List (Column B)
car accessory
car covers
truck covers
car accessories

I think VLOOKUP only works on lists.

View 6 Replies View Related

Multiple Vlookup Results As A Text String

Apr 24, 2009

I have searched other postings but can’t seem to find the answer.

I was wondering if anyone had a solution on how to string multiple Vlookup results as a text string so that if I Vlookup “Pear” it will return “Red, Blue, Green” in adjacent cell
i.e

A B
1 Fruit Colour
2 Pear Red
3 Orange Purple
4 Apple Green
5 Banana Yellow
6 Pear Blue
7 Pear Green
8 Banana Black
9 Orange Green
10 Apple Black

Is Vlookup the right function to go with here?

View 9 Replies View Related

ADVERTISEMENT