FIND Value In String Perform Lookup From Range

Oct 6, 2009

I have account information with 4-digit customer location identifiers beginning at character 9 and ending at character 12:

3600820040010500050006080000360082004004050005000503000036008200400105000500068900003600820040050500050006880000360082004001050005000402000036008200400105000500052320003600820040030500050005271000

No matter what changes in this string, these four digits (9, 10, 11 & 12) will always be in the same position.

What I would like to do is (I think) use the FIND funtion and then perform a VLOOKUP against an already defined table/range which identifies the actual location in the destination cell. Will FIND be the easiest for me to do this our should I use something else?

The what to interpret characters 9-12 (using 3600820040030500050005271000 as an example):

3600820040030500050005271000

4003 is the location information which consistantly breaks down:

4 = Region Name
003 = Office / City (001 thru 024)

There are 7 possible regions with 24 possible Offices/Cities. I want to believe I can make this lookup happen using VLOOKUP tables that break them down - but I just don't have the brainpower to identify those 4 digits and then break them out for a look up.

View 9 Replies


ADVERTISEMENT

Find Most Common Occurring String Based On Lookup Value?

Dec 4, 2013

Find the most common occurring string in my spreadsheet based on a lookup value. For example, if my table is as follows:

Capture.PNG

- I want to calculate the most common value chosen by user 234 (A in this case), user 524 (B in this case)... and so on.
- I want to calculate the percent of the chosen value. So in the previous point, A was chosen ~67% of the time by user 234... and so on.

I'm not sure which formula to use. I am able to find the most common occurring value in column B using the formula below (which I found here), but how can I do that for a specific value, a user ID in this case?

View 4 Replies View Related

How Do I Perform An Array Multiplication If A Cell Contains A Specific Text String?

Feb 17, 2009

Sum every instance of [SPREAD]*[SIZE] where [NAME] occurs within a text string.

Please note, I'm trying to do this without creating a new column [SPREAD]*[SIZE].

View 11 Replies View Related

Lookup Part Of Text String In Userform ComboBox And Search For Value In Sheet Range

Feb 12, 2014

I have a worksheet with following values:

A
B
C
1
Shorts
75

[Code]...

Also I have a Userform with 2 ComboBoxes named "ComboBox1" and "ComboBox2". Values in ComboBox1 is "Pull my pants", "Eat my shorts", "Socks for everyone".

What I would like to do is to search though column A and look for any of these textstrings in my selection in ComboBox1 and return the value from column B in column C.

Example: "Pull my pants" is selected in ComboBox1 then the value "pants" should be found in column A and value in column B (25) should be entered in cell C2.

I am fairly new to VBA and have spend hours searching Google and found some formulas like InStr and VLookup. My problem is that I am not sure if these statements will do the job and how to combine them.

View 1 Replies View Related

Find The Lest Common String-value In A Range

Feb 1, 2009

Everything is explained inside the attached WB.

View 4 Replies View Related

Find String In Named Range

Dec 23, 2011

I have a text string "Area" which relates to an entry in a named range "AreaCodes", which is stored on a non-active sheet. How can I activate the cell contaning the string other than using this, recorded, code

Code:
Sheets("Sheet3").Select
Range("A1").Select
Cells.Find(What:=Area, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate

View 9 Replies View Related

Find Method Returns A String Instead Of A Range

Jun 24, 2009

For some reason, it seems that this following line returns the value its supposed to be searching for instead of the range where it finds the value.

View 5 Replies View Related

Excel 2010 :: Macro To Perform Find And Replace Function

Apr 8, 2014

I have two sheets. One is named sheet1 where all my data is and sheet2 where all my values are. I want to do a find a replace on column N (sheet1) using the data in sheet2. Column A on Sheet2 has all the values that are found in column N and column B on Sheet2 has what the data should change to.

So for example:

Sheet1 says the following on column N:
cat
dog
lion
bear

Sheet2 says:
Column A
A1: Cat
A2: Dog
A3: Bear

Column B
B1: 2
B2: 8
B3:15

I want the values on column N to be replaced with 2, 8, 15 and so forth. I use excel 2010.

View 1 Replies View Related

Find Most Common (x5) Numbers Within Numerical String Range?

Apr 2, 2014

I have a range of cells containing a string of numbers separated by ';' e.g.

COLUMN A COLUMN B
IDENTIFIER_011122;1942;2011;1869;2642;2729;2731;2655;2678;2341;2347;2354;2332;2506;1809
IDENTIFIER_021122;1942;2011;1869;2642;2729;2731;2655;2678;2207;2209;2217;2220;1880;2506;1809
IDENTIFIER_031122;1942;2011;1869;2642;2729;2731;2732;2648;2255;2207;2217;2220;2387;1880;2506;1809
IDENTIFIER_041122;1942;2011;1869;2642;2729;2731;2732;2678;2207;2209;2217;2220;1880;2506;1809
IDENTIFIER_051122;1942;1971;2011;1869;2642;2729;2731;2648;2678;2342;2354;2355;2207;2209;2217;2220;2398;1880;2506;1809
IDENTIFIER_061122;1942;2011;2169;1869;2642;2729;2731;2732;2678;2341;2343;2347;2354;2207;2209;2217;2220;1880;2506;1809
IDENTIFIER_071122;1942;2095;1869;2573;2587;2648;2655;2673;2207;2209;2220;1880;1809
IDENTIFIER_081122;1942;2095;1870;2642;2648;2673;2678;2341;1880;2506;1809

I am looking to find the top 5 most common/repeated numbers within this range. I have found a formula to work out the largest number within the range:

=MAX(IFERROR(0+TRIM(MID(SUBSTITUTE(";"&B3,";",REPT(" ",LEN(B3))),(ROW(A$1:A$200)*LEN(B3))-1,LEN(B3))),0)) (REF http://www.excelforum.com/excel-gene...e-numbers.html)

How to modify it to work with e.g. the mode function.

I know I could separate all the numbers out using 'text to columns' but I will need to work this out regularly on large quantities of data ...

View 13 Replies View Related

Lookup Functions Find The Address Of The Minimum Value In That Range

Sep 19, 2009

I have a range of cells (say A1:D8).
I wanna find the address (say $B$2) of the minimum value in that range.


This command does it but it only works on a column. it does not accept a range spanning more than one column.
=CELL("address",INDEX(B2:B7,MATCH(MIN(B2:B7),B2:B7,0)))
this will tell me the address of the minimum value.. but in a column... I need a range of many columns.

So I tried converting things to a Table.. but it never worked. basically the MATCH command is my problem. It only accepts single-column-ed ranges.

View 13 Replies View Related

VBA Check Name In Range If Not Perform Action?

Apr 12, 2013

If a name is entered in cboRepName combo box, I want to check if the name matches the list of names in the named range "Names" found in sheet repInformation. If the name is not found, then perform tasks.

Here is what I have so far, of course is not working, but I think I am close.

Code:
Private Sub cboRepName_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim repName As String
Dim nRange As Range

[Code].....

View 2 Replies View Related

Perform A Two Column Lookup That Returns A Value From Another Column?

Feb 1, 2010

I have a table with 3 columns. See below for data (ignore dashes):

COLUMN X-----COLUMN Y-----COLUMN Z
Project 1---------21 Dec 09---------$70
Project 2---------21 Dec 09---------$85
Project 3---------21 Dec 09---------$90
Project 1---------1 Jan 10-----------$85
Project 2---------1 Jan 10-----------$95
Project 3---------1 Jan 10-----------$110

As you can see, COLUMN X contains Project Names, COLUMN Y contains Dates and COLUMN Z contains Monetary Value.

Here is what I need to do: I need a formula/function that will lookup "Project 1" in COLUMN X and find the latest corresponding Date in COLUMN Y (for this example, 1 Jan 10) for Project 1 and then display the corresponding Monetary Value from COLUMN Z (for this example, $85).

I have tried using VLOOKUP, but since the data must be sorted it won't work. I tried using INDEX(MATCH), but can't get it to work. My goal is that the cell will be dynamically updated every time a user inputs updated data for a certain project based on the latest date.

View 3 Replies View Related

IF.. Contains...then: Search Through The Text String In That Cell And Find A Certain Word, Find And Retrn A Value

May 23, 2006

I am having trouble getting my IF statement to test if the cell contains the text "sale" return "X" if not "Y". I need it to search through the text string in that cell and find a certain word, and if it finds that word, retrn a value. I am really having difficulty with is what symbol or function do I use for the logical test? (i.e. =, <>, MATCH, INDEX?)

View 4 Replies View Related

Lookup Formula: Find The Longitude And Latitude Data From My "lookup" Sheet

Jan 28, 2009

In my workbook I have multiple sheets but I'm attaching a very simple workbook to demonstrate what I'm trying to accomplish. In my "Lookup" tab/sheet. I want to have known Latitude and Longitude data that will exist in columns A&B. Columns C & D will have address numbers and Street Name. I would like my lookup formula to find the longitude and latitude data from my "lookup" sheet, when the matching address information is typed in, in my 2009 sheet. I have to keep the street numerics and street name separate on this worksheet as well. I believe I'll need two separate lookup formulas as I need these formulas to start in cell G4 & H4 in my "GeoCoding1" sheet. Is it possible to have four columns of data to be viewed in a lookup formula? I tried this formula in cell G4 (GeoCoding1 sheet)

View 3 Replies View Related

Lookup For A String Of Numbers

Oct 20, 2008

I have a list of data, 7 coumns x 4000 rows with integers in them. b2:h4173

I would like to write a forumula(e) which will tell me which 3 integers appear in the same row most often.

View 9 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 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

Lookup But First Finding A Unique String?

Jul 3, 2014

mockup.xlsxStarting out with a 9 by 11 grid.

Column A will have times that need to be referenced in a lookup. (Ex Sat 8:00am)

Row 1 will have basball field codes reference with a lookup. (Example Field 4

Cells B2:I11 have distinct codes or possibly nothing. (An example would be: Tier 1 A1-A3)

These codes will move around from time to time. They will always remain distinct within the grid, but could appear in any of the cells from B2:I11.

I have 2 columns I want to populate with values from the grid.

First is Fields. I want the first cell to populate with the baseball field found in row 1 of which the code Tier1 A1-A3 falls under.

For example if this season Tier1 A1-A3 is found in the column under Field 4, then I need to return Field 4 as a result.

Second is the time. I want the time to be populated based on what row the Tier1 A1-A3 code falls in.

For example if Tier1 A1-A3 is in the Sat 8:00am row, then I need that value returned.

In short, I need to search the grid and find a given value. I then need to use a lookup (or other tool) to return the first value in that row. And secondly via lookup return the first value in that column.

edit: added spreadsheet example. First sheet is grid in question, 2nd sheet is a typical schedule.

The first Team (Scared Hitless) is A1, the team they play first is Sands Duel Fuel (A3) They play at 11:00am on Field TR3.

You will see on the grid under TR3 and Sat 11:00 the code Tier 4 A1v3. This code is what I want to search and correlate into times on the schedule rather than doing the data entry by hand.

View 1 Replies View Related

Lookup Data With Matching Criteria String?

May 2, 2014

how to figure it out this lookup problem (lookup using partial string of match)...

View 5 Replies View Related

Lookup Text Within String Return Category

Jan 29, 2010

I need to look within column A (which contains text strings), match label in column c (which contains text for label), and return result within column B (titled category label).

Essentially, I want to label my data based upon a specific element found within column A with a label list in column C.

I have attached an excel file that shows the current problem. Nested if(isnumber(search( statements can perform this, but I yield too many arguments error after three labels?

Isn't there a way to do this with Vlookup or Hlookup?

View 9 Replies View Related

Creating String / Lookup / Validation From One Reference Sheet

Apr 18, 2014

I have an endless list of information that I need to turn into a text string, lookup with a validation on the end.

The easiest way is to show you so I've attached the info and what result I want.

Attached File : Test.xlsx‎

View 3 Replies View Related

Search For Text Within A String, Lookup And Then Return Item

Sep 30, 2009

I have a spreadsheet!

I have two sheets, one of which contains film names and the other contains our tag structure for our website (which is a list of tags, their keywords and the primary parent channel they live under).

What I am trying to do is search the film titles and if any of the words match either the tag name or keywords then return the relevant channel.

Example:

Sheet 1 - Films
How To Apply Bridal Makeup
How To Fight A Donkey
How To Write Excel Formulae

Sheet 2 - Tags
Channel / Tag / Keywords
Tech - Microsoft - windows vista xp word excel
Tech - Computers - internet pc
Tech - MP3 Players - iPod Zune

So, for film 3 on Sheet 1 it would recognise the word Excel in the keyword list and return the channel Tech.

View 12 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

Make A Lookup On A String Of Words From One Of My Worksheets And Hyper Link

Oct 9, 2008

I want to make a lookup on a string of words from one of my worksheets and I also whant to make a hyper link so that when I click on the lookup command I should pop to the other worksheet were my database is.

View 2 Replies View Related

Find Numerical Followed By X In A String

Mar 24, 2013

find the first numerical in a text string that is immediately followed by an x

so that i can extract the packing size from packing description workbook attached

DESCRIPTION
PACKING SIZE
SUNFLOWER OIL 12X1LT
12X1LT

[Code].....

View 5 Replies View Related

Find Country Name Into String?

May 6, 2014

i am trying to find country name in cell consisting of sting, i applied "=INDEX(K2:X2,MATCH(TRUE,INDEX(ISNUMBER(SEARCH(K2:X2,A2)),0),0))" this formula i am getting desired result but i am not able to apply ctrl+d since all cell reference are iterating.

View 8 Replies View Related

VBA; Find String And Copy

Jan 31, 2008

I would like to call up a find box, then be able to search several worksheets for a string, to copy and paste the related row to a new worksheet.

I've gotten as far as being able to search for a string and get the row pasted, as long as the string is defined in my macro. Is there a way though to be able to call up a find box first though, and search for the input variable? (alternatively, I can only imagine a separate piece of code for each variable, of which there are many, and I am actually trying to save time.

View 14 Replies View Related

Find Last Row With Text String

Jun 16, 2009

The following line finds last "non-blank" row in a column. I have a column that has formulas in 10000 rows. Most of the time only first 7000 rows will have data, other cells below will have "". I need to find the last row with real data, not "".

Is there a way to add a condition or check to the following line?

View 6 Replies View Related

Find String, Add Values

Aug 2, 2009

1) Find strings "CA", "Ca", "ca", "California", "california", "cali", "Cali" in Column F

2) If the string is found, then record the Real value at Column D of that row in Column I

3) Continue this until there are no more strings in Column F

4) Add the values recorded in Column I and display the final value at the bottom of the list.

View 2 Replies View Related

Find Numeric Value At End Of String

Feb 3, 2006

say I have a string that looks like this

C:Documents and Settingsme[test 2-2006.xls]Sheet1

I want to extract the DATE prior to .xls. I can get to this:
C:Documents and Settingsme[test 2-2006

It is possible that the filename would have numbers in it prior to the date.

I'm trying to figure out a way to get to the last SPACE in the string.

View 13 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved