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
ADVERTISEMENT
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
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
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.
View 9 Replies
View Related
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
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
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
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
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
Sep 22, 2009
i have attached an example to illustrate the data source and result expected.
View 3 Replies
View Related
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
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
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
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
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
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
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
May 5, 2009
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 10 Replies
View Related
Oct 22, 2012
I need to compare a string from say A1 to a range of cells in another workbook and if found return the value of a cell on the same row.
Something like - look up string value in A1 in other workbook.sheet1.A1:A65535 if matched return the value of matched row column b.
OR
IF Otherworkbook.sheet1.A20 = A1 return the value of B20
View 6 Replies
View Related
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
May 21, 2013
I have been using an IF,ISNA,VLOOKUP formula as follows which I am sure you are all familiar with :
=IF(ISNA(VLOOKUP(K7,Orig!A7:B35,COLUMNS(B7:B35)+1,0)),"",VLOOKUP(K7,Orig!A7:B35,COLUMNS(B7:B35)+1,0))
This formula works correctly, displaying the lookup value for K7. My query is between the"" I can place text to display when K7 is blank and this works correctly too. However I would like to place a formula in here. The formula is VLOOKUP(I7,Orig!A7:B35,COLUMNS(B7:B35)+1,0 i.e. the lookup value is now I7 and not K7 when K7 is blank.
I have tried the following and variations based on what I know but they return errors.
=IF(ISNA(VLOOKUP(K7,Orig!A7:B35,COLUMNS(B7:B35)+1,0)),(""& VLOOKUP(K7,Orig!A7:B35,COLUMNS(B7:B35)+1,0),VLOOKUP(K7,Orig!A7:B35,COLUMNS(B7:B35)+1,0))
Any better way of using I7 as the lookup value when K7 is blank.
View 7 Replies
View Related
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
Sep 19, 2012
I have a udf that returns a string to the cell. The string is made up of multiple string "objects". What I am wondering is if I can set the font color of certain objects so that when the final string is built and returned, the font of those portions is set.
Ex. of simple idea (this is not actually my code, just a way to illustrate. I realize there is no point to this UDF):
VB:
Function StringReturn (Str1 As String, Str2 As String, Str3 As String) As String
StringReturn = Str1 & Str2 & Str3
End Function
Now what if I wanted Str1 and Str3 to be blue, and Str2 to be red for example. So that when the UDF calculates it would return: Str1Str2Str3
View 2 Replies
View Related
Aug 14, 2014
Wondering if it is possible to generate a random 4 digit number based off an alphanumeric string?
Example;
Cell A1 has 123XVF1234
Cell A2 has 321AFW4321
In B1 I would like to have a 4-6 digit number that is generated based on the alphanumeric data in Cell A1 (and so on down the list). If that is possible, I would also need to be able to convert back the 4-6 digit number back to its original alphanumeric value
Example;
If B1 returns 643562 it would need to be able to be converted back to 123XVF1234
View 7 Replies
View Related
Apr 7, 2014
In sheet1 I have a simple database consisting of 5 columns of data
Column A : Name ie James Jones
Column B : payroll number ie 123456
Column C : shift times ie 1245-2124
Column D : job title ie floor
Column E : comments ie A/L or 0600-1500
what I would like is some code that will go down Column E and if a 'time string' ie 1300-2130 is found then copy this string and paste into corresponding value in column C. If a text string is found ie A/L or Sick or anything like this then ignore and move onto next cell, loop this until all cells in column E have been checked.
View 4 Replies
View Related
Jan 10, 2009
I need to create a string of all the Acc Number but I dun not wan any repeat of the same Acc number in the String . The number of Acc number to be put in the string may varies sometimes. It may varies from 3 to 1000 or more . Is there a way to write a general formula to create such string?
View 4 Replies
View Related
Aug 27, 2009
I have a situation where a word (in this case "Restaurant") is misspelled in a list of about 78,000 location names. The location names are in one column, and the [misspelled] word "Restaurant" is anywhere between the first word of the string, to the last, with any amount of alphanumeric/symbol characters between. For example:
Alice's Restaurant
Alli's Restaurant & Bar
Alexis Restaurant of Waukesha
Amigo's Mexican Restaurant #2
I want to replace any misspellings of the word with correct, but since the list is so long, and the way the word is misspelled varies so much, going through manually is entirely too time-consuming.
Some of the variations I've seen so far are Resta, Restau, Restaur, Restuara, etc...
Is there a way to search and replace cells that contain the text in any location of the string? Specifically, where ever there is JUST "Restau", replace with "Restaurant", regardless of where it is in the string? This way, trailing text is not deleted or manipulated, i.e. "Alice's Restaur and Bar" will change to "Alice's Restaurant and Bar".
View 14 Replies
View Related
Apr 11, 2013
I have a spreadsheet which has "Employee: [agent 1 name]" in column A and it may or may not have the word "Break" in the same column before it mentions "Employee: [agent 2 name]". The amount of data between agent 1 and agent 2 varies and am needing code which will insert a row above "Employee: [agent 2 name]" if "Break" is not found, and add the word "Break" in column A on the inserted row. I would need this to loop through the spreadsheet until all 100+ agents have been searched.
I'm also needing this done for the word "Meeting" and would insert a row 2 rows above the next agent.
View 6 Replies
View Related
Feb 18, 2014
I'm using a userform to create a new sheet. The form already creates the sheet and names it what was typed into the userform. Now I want it to place that variable in a cell along with a string. the following code will place the variable from the form (tbname) into cell b5.
View 2 Replies
View Related
Sep 4, 2012
I am working with flight numbers and want to split out the letters from the digits. Examples,
BA1234A
BA123
EZY4566H
BE7893B
In column A I need the first two or three letters only,
BA
BA
EZY
BE
In column B I need everything to the right of what appears in column A
1234A
123
4566H
893B
etc.
View 7 Replies
View Related
