Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Partial String Vlookup

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.


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.

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Partial String Lookup
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:

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 Replies!   View Related
Macro For Partial String In Text
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 Replies!   View Related
Delete Row Based On Partial String Criteria
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 Replies!   View Related
Retrieve Record On Partial String Search
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 Replies!   View Related
Get Partial String From Active WorkBook.Path
I tend to stumble when trying to pull certain characters from strings, not sure how to stop and start my search.

View Replies!   View Related
Search Partial String, Return Value In Next Column, Vba
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 Replies!   View Related
Lookup Partial String To Return Data From Corresponding Column
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 Replies!   View Related
Sort Pivot Table Based On Partial String...
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 Replies!   View Related
Summarize Data Based On Partial String Matches
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:
how do i split it so as to assign two variables to have 'new' and 'polygon'.

View Replies!   View Related
Partial String Check In Cell Against Range For Return
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 Replies!   View Related
Partial Vlookup Match
In cells A1:A10 I have the following list;


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 Replies!   View Related
Vlookup Of Partial Matching Values
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 Replies!   View Related
Partial Text Lookup In Text String And Return
Cell H1 has a variable string of references for eg

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 Replies!   View Related
VLOOKUP String Of Text
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 Replies!   View Related
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 Replies!   View Related
Vlookup Part Of String
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 Replies!   View Related
VLOOKUP Part Of A Text String.
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 Replies!   View Related
Vlookup Part Of The Text String
Is it possible to lookup if at least 8 characters of a text string is found in another text string?

View Replies!   View Related
#VALUE! Error Creating String For VLOOKUP
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 Replies!   View Related
Vlookup Formula With Date String
i have attached an example to illustrate the data source and result expected.

View Replies!   View Related
Vlookup To Search For Part Of A String?
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 Replies!   View Related
Vlookup To Return Second Occurrence Of String
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

HOME 20 6 1 0
AWAY 20 7 2 0
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:

HOME 20 6 1 0 2 0 1
AWAY 20 7 2 0 3 1 4

View Replies!   View Related
Vlookup On Only Part Of A Text/number String
I am trying to reference a Name of a place from an order number. To illustrate, University Park, IL can have an order # of 6598641373. The only thing is, all I need to reference is the first four digits, 6598. The other worksheet does not have city and state names, they only have the order #s.

View Replies!   View Related
Multiple Vlookup Results As A Text String
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

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 Replies!   View Related
Show A Partial DOB?
I have a date of birth in cell "B13". It's formated as xx/xx/xxxx. I'd like to copy this into another cell where I have annual holidays listed, but, I'd like to have it formatted as mm:dd - omit the year.

View Replies!   View Related
Cell Partial, VBA
I have a cell that contains a string which is always formatted the same. "Dated between Date1 and Date2" I would like to put Date2 into a variable; however, I don't know how to get just that part of the cell.

View Replies!   View Related
Partial Match In An Array
column A

cell B1

I am looking for a formula (Vlookup, Match, etc) that will look for an entry in column A that matches part of the string in cell B1 (not the other way around i.e. using wildcard)

View Replies!   View Related
Partial Or Posible Match
select first the 2 ranges i want to compare
select the columns used like criteria

compare and find the posible match and copy on an new excel the result

possible matching


comun value 28244654



View Replies!   View Related
Counting Partial Strings
I don't know what's wrong with this it's not working..


i just want to count all the cells from a2 to a20 that has a word "radio" from that cells. Since a2 until a20 have different words like tv power, radio blaster (this should be consider because there's a radio word on it) and so on.

if i use only =COUNTIF(A2:A20,"radio") the word should be exact "radio" and it wont consider the word "radio blaster".

View Replies!   View Related
Autofilter By Partial Date
I have a list of data that I'm sorting by date using an autofilter, but the way I have to set my criteria is by fiscal years and quarters; so I'm only concerned with the year and month parts of a date like 11/20/2006.

For example: all the dates in the list are in the standard US mm/dd/yyyy format, and for one filter I want to display only records with a date in the year 2005. I could use fixed dates starting with the first of the year and the last of the year, but I'm wondering if there is a better way.

I tried variations like this (but nothing worked).

varYear = 2005

Range("A1:K48").AutoFilter Field:=3, Operator:=xlFilterValues, Criteria1:="<=*" & varYear

View Replies!   View Related
Look Up With Partial Text Search
I am looking for a lookup to look for text within Column A that matches text in column D and brings the word in that is in column E ...

View Replies!   View Related
COUNTIF With Partial Match?
I have a column with a different alphanumeric strings in each cell. Some of these values have a portion that is common. For e.g.:

Example kealey 1980
Example john commission
Example (hard sell)

Is there a formula to return the number of cells that have "Example" in them?

View Replies!   View Related
Partial Word Search
Is there a command out there that i can use to search a cell or textbox for part of a word, eg. my word being phone, and for it to look at cellphone and recognize it.

View Replies!   View Related
Linking Partial Cell Data
I have two cells on Sheet 1 - Cell A1 and Cell A2.

Cell A1 is the first cell that will receive either a number, or nothing. When it receives a number, the number will always contain five digits. (For example: '23456'.)

The contents of Cell A2 depend on the contents of Cell A1 - If Cell A1 contains a five digit number, then Cell A2 will display that same five-digit number with '-1' following it. (In our case: '23456-1'.) If Cell A1 contains no number, then Cell A2 will just be another ordinary cell allowing the user to enter whatever he/she desires.

The extension in Cell A2 should remain editable at all times - if the user wishes to change the '-1' to a '-2', for example, he/she should be able to do so without any error messages appearing. However, the user must not be able to change any of the first five digits in Cell A2 as long as Cell A1 contains data. A message should appear stating that changes to the first five digits should be performed in Cell A1 - which would then change Cell A2 accordingly.

Also, if the contents of Cell A1 are erased for any reason, Cell A2 should keep the five digit number, but lose whatever '-x' extension it contained.

Can this be done using Data Validation?

View Replies!   View Related
Deleting Partial Content In A Column
I have a sheet with 3 columns, A B and C

column B has content such as:

term 1 (c: text a)
term 2
term 3
term 4 (c: text b)

desired output:

term 1
term 2
term 3
term 4

Can someone create a macro for me to delete the (c: ...) in Column B but leaves all of the other column data alone.

View Replies!   View Related
Extract Partial Contents Of A Cell
I need to extract partial contents of a cell.. right now in the cell it looks like

address: 9999

so I want my code to see the cell... dropp the "address: " and only retrieve 9999.

I know it's on google somewhere but I can't seem to describe it properly for the right results to come up.

View Replies!   View Related
Linking Partial Data - Macro
I have had much assistance with this complex (I think it's complex) macro in the past, however, there are still a few bugs that need to be worked out in order for it to be completely efficient. Any help would be really appreciated, and I'll do my best to explain what's going on at this point:

I have a worksheet called the 'Data Entry Page'. You will find it attached. Here are the requirements:

The PO Number cell will only allow ten digits with a dash, and will receive values such as 1200012345-1.

The Part Number cell will receive a value which will usually be six digits long with a dash, and will receive a value such as 654321-1.

The Identification Type cell will receive at least one of the following values, or two or more of the following values separated by '&' (Note the spaces between the digits):


or (some combination examples):
IA & I I I
I I & I I IA

View Replies!   View Related
Copy A File By Specifying A Partial Filename
I'm attempting to copy some files into an archive folder in the same main folder. These files appear weekly in a folder that contains numerous other files.

Each of these files has a filename such as:


with time being the time that the report was run.

Every week the filename stays the same but the time and date changes. I was hoping to be able to simply use the "Filecopy" command and specify the source file with a "*" after the main file name. However this does not seem to work.

Is there a simple way to do this? (I dont want more than a few lines of coding really as I will be duplicating it for each file and then changing the file name as needed.

View Replies!   View Related
Adapting This To Cope With Partial Matches
The solution to a challenge here does almost exactly what I need except it doesn't manage partial matches.

Function MatchColors(strValue As String, rngList As Range) As String
Dim regEx, Matches, i, strResult, bFlag

'Create and set the parameters for the regular expressions object
Set regEx = CreateObject("vbscript.regexp")
regEx.Global = True
regEx.IgnoreCase = True

View Replies!   View Related
Partial Matching Using Data Validation
I have set up data validation on a cell that produces a
drop down list of part numbers.

Is there a way that I can enable a partial part number search?
If I type in 354, all part numbers that start with 354 will be in the drop down list for me to choose from.

View Replies!   View Related
Partial Match Boolean Type
I have strings in cell a1. I will put a formula in a3 if string "xyz" is found inside a1, it will show value from a2, else shows nothing.

i tried simple formula cell a3
here it is exact match, i need partial match, or multiple partial match in a single cell. How to do it?

View Replies!   View Related
Workday - Adding Partial Days
When I use the formula below it works great;

= J10+(1/$D$10)

J10 = 3-1-07
D10 = 1.5 (Production Rate example .2,.5, 1.5 rate per day) The result is:

3-5-07 and so on...

however, when I try to include only workdays, it does not calculate correctly.

= WORKDAY(J10+(1/$D$10),1,Holidays!B75:B88) with the same definitions as above the result is...


View Replies!   View Related
Importing A Partial Text File
I have VBA code that imports a text file using a fixed width separator within the code. The code is working just as I need it to work and placing the information/data in the columns that I have specified in the code.

The issue I have is I only want to import certain data that is contained within the text file. It is going to be a little hard to explain, so I have attached a sample of the text file with dummy data to better explain. The file is broken up into different segments. Each segment is identified by a “Base Number” in column A of the text file (once it has been converted into an Excel file).

For my example, I only want to import data for Base Number 38451. The information for this segment is easy enough to identify when the data starts and when the data ends. As in this example, the first occurrence of 38451 begins at row 257. Once the first instance of this number is identified, the first row of data that I require is always two rows after.

I think the coding should go something like this, but I just can’t figure out how to put it into my
If cell in column A = 38451 then
- start importing from 2 rows down
- continue to import until next row in column C is not = date

If row in column C = blank then
- search in column A until cell = 38451

If cell in column A = 38451 then
- start importing from 1 row down
- continue to import until next row in column C is not = date

- stop import

I have included two files. (1) the text file with the data (2) the Excel file. There are two tabs in the Excel file “ReconSheet” (contains the data that is imported from the text file) and “Desired Result” (this is how I want the end product to look).

View Replies!   View Related
Using Partial Strings As Argument For Sumif()
I have a 100 element header row. Each cell represents an account. I want to sum the amounts of each subsequent row if the word "saving" shows up in the header. For instance, there may be "saving 1" "Savings for Xmas" "Savings 2" in the header row, and I want those to trigger the sumif function.

Basically I want something like sumif(B2:B101,exact(left(B2:B101,6),"saving"),A2:A101)

View Replies!   View Related
Creating Partial Number Ranges
I need to create a consecutive number range
from 10,770 to 14,194
(using only numbers that end in 0,1,2,3, & 4)

In other words, with No numbers that end
in 5/6/7/8/9

Example: 10,770

... and so on, upto 14,194

View Replies!   View Related
Lookup Data With Partial Match
My database includes data about period (column A), a product code (column B), Turnover previous year (column C), Trend (column D) and Turnover this year (column E). In column C i want to have the turnovers from a particular product code from the previous year. The difficulty is:

1. that the product code can vary
2. not all periods are availabe.

How can i formulate a formulate that shows the turnover from the previous year, and in case there is no data from the previous year it should show "n.a.". This formula must also take into account that product code can vary. In my example: C3 should show 1296, C4 should show 877, C5 should show 884 and C6 should show 960. In case the turnover of period 200104 was not available C3 should show n.a.

View Replies!   View Related
Lookup Partial Match Between 2 Workbooks
I have a spreadheet which requires data from another spreadsheet. Spreadsheet 1 is only project numbers, spreadsheet 2 has all the customer data in it. I have used a 'vlookup' function to find the project number and copy the appropriate information over. The problem I have is the projects are a 6 number format (eg 531300) in both spreadsheets. In the sheet I want to complete, the project numbers only match the first 4 numbers, as they are seperated into sub-projects. Therefore the data in the data spreadsheet will only have a row for 531300, yet the one I fill in might have rows 531300, 531301, 531302 etc. Is there a way to run the 'vlookup' and only match the first 4 numbers. The current formula I have in the first cell is as follows: =VLOOKUP(A4,'[Project Database_Updated 04-30-08.xls]Sheet1'!$A$1:$E$1007,2,FALSE)

View Replies!   View Related
Picking Partial Parts Of A Cell
I got some data like this


The gap between each word and the word "US" is uneven. sometimes it's 3 spacebar apart, and sometimes 4.

I am trying the pick the part not including US. I tried =IF(ISERROR(SEARCH("US",A1)),A1,LEFT(A1,SEARCH("US",A1)-1)). It works but when I use match function to look up the word. It returns N.A. As the output is not just CTAC(4 characters), but 5 characters(including blank). The problem is the gap between between the word and "US" is different for each cell. so I cannot just minus the same character in the above formula (e.g. -2 if there is one blank cell). Anyone can tell me how to pick out the word and with only the number of characters in the word?(no blank)

View Replies!   View Related
Lookup By Partial Text Match
consider the following master data

1AB Cat
2HB Dog
3BX Rat
1ABC Tiger

Now I have the following data, which will lookup the master data presented above and return the corresponding animal - For eg.

1AB29489284 CAT
1ABC0395935 Tiger
2HB29492492 Dog

The above CANNOT be done by VLOOKUP, since the class - Tiger, needs to be differentiated from CAT by the 4th letter. How can the above problem be solved???
(This question recently came in one of the Recruitment Exams)

View Replies!   View Related
Countif Of Partial Cell Text
I have a problem, in a table, the cell H5 has this formula. .=IF(E5=20, COUNTIF(G5,">=10y")+COUNTIF(G5,">=12y") +COUNTIF(G5,">=14y")+COUNTIF(G5,">=16y")+COUNTIF(G 5,">=18y")). every time that G5 has a value >= "10y"or "12y" until "18y", H5 should add 1 or 2 until 5. when I fill out C5, with an answer value in G5 of + 18y, H5 = 5, everything works fine. writing 02/08/1988 in C5 the result in H5 is 5, but when I change the value of C5 for 02/08/1999, H5 should be "0" but it continues with the value 5.

View Replies!   View Related
Copyright © 2005-08, All rights reserved