Using List Of Data With Wildcards In VLookup Formula As Lookup Value?
Mar 8, 2013
I have a list of rules on how to group account numbers. An example is below. I have a very long list of full account numbers (no wildcards). There are roughly 75 rules on how to group the over 40,000 accounts. I want to use the vlookup to determine what group each full account number (always 11 characters) would belong to? It seems that the vlookup isn't treating the ?'s as wildcards. I can use the full account number rules with the wildcards in the cell in the sumif function though.
So ultimately I would like to have my list of account numbers like 40000000000 and 40000000001 know that they belong in group 1 based on the rule table below.
Full Account Number
Account
Subaccount
Group
[code]....
View 4 Replies
ADVERTISEMENT
Dec 4, 2007
When I use a lookup to look for "*"&A1&"*" where A1 contains "AB" I get a value not available error despite the fact that in the postcode table there are loads of postcodes beginning with "AB...". Is it the multiple entries of "AB" in the lookup table that will be messing this search up?
View 9 Replies
View Related
Dec 15, 2009
I have a workbook (Data) that I am entering "job title" into column G. Based on key words in the job title I am then manually entering "level" into Column V.
I have created a worksheet (Level Matrix) that has the following
Column A = Job title
Column B - Level
Column C = Exceptions
What I have in column A are the key words - as an example *Director* (wildcard Director Wildcard); in column B is the word Director
What I want the system to do is look for the word DIRECTOR anyplace in the job title entered into Column G of the data worksheet (hence why I have the title between wildcards. IF it finds it then I want to add the level automatically (from Column B of the Level Matrix worksheet).
However if the compare finds any of the words in the title that match any of the words in the Exceptions Column (Column C of the Level Matrix) then I DO NOT WANT To autoload the Director level- it would load DNA. As an example- an Art Director would not load "Director" becasue the word "art" is one of the words in the exceptions column of the Level Matrix.
View 9 Replies
View Related
Feb 5, 2014
I have a 14 000-item list of product codes that are generated from different attributes. What I am trying to do, is to create a cover sheet where the user can select attributes from dropdown lists and get the first matching product code. I have tried using index/match, but I am struggling with wildcard lookup.
Example of generated product code:Cell A1:
ABX**J
Here, AB is the product group, X is the pressure class, ** is material (which is, for the example, unknown), and J is the end connection. What I now want to do, is to look in the long list of codes and find the first match:
Cells A2:A5:AAY02J
ABY03F
ABX01J
View 1 Replies
View Related
Oct 25, 2013
I have a cell that I must remove the first 2 characters "RO" for each value in a column on a sheet called RAW DATA and put into a cell on a sheet called ROSS DATA. Some of the values in that cell have 3 digits after the RO and some have 5 digits. To do that I used
=REPLACE('RAW DATA'!A3,1,2,"")
Then I need to use this new resultant string as the lookup value in a VLOOKUP. The VLOOKUP will be looking at a named range called DAP on a sheet called DAP, in column 5 for an exact match and I need it to return that value to the cell.
I have tried using the indirect to no avail in different ways, and not sure that I fully understand the usage.
View 9 Replies
View Related
Jun 26, 2008
I'm running against this problem: a file should be run whenever people want.
however, the last part to complete the file is that the file from yesterday should be opened.
From this file data will be extracted via the VLookup formula...that's the plan.
However, with dim statements it won't work.
for now the code is:
Dim mynum, mysearch, this1, that1, other1
'Open the file from yesterday. Define the date string of this week and last week
mysearch = Left(mynum, 8) & Right(mynum, 2) - 1 'mynum is a date string like 2008-06-26
this1 = "Outstanding invoices " & mynum & ".xls"
that1 = "Outstanding invoices " & mysearch & ".xls"
Set other1 = Workbooks(that1). Sheets("Raw"). Range("comments")
ChDir "Z: FINANCEF&AAPInvoices overdue"
Workbooks.Open Filename:= _
"Z:FINANCEF&AAPInvoices overdue" & that1
the "this1, that1" etc appear when you point with your mouse in the code as correct but
the VLookup function won't work: it takes a long time and when interupted the macro the cells show the VLookup formula with "[that1]" instead of the workbook's name.
I also tried to dim that1 as workbook but that did not help.
View 3 Replies
View Related
Apr 9, 2009
As I copy and past my lookup formula down the page it is changing the lookup range which I think is what is giving me so many #N/A results. My first Formula is
=IFERROR(VLOOKUP(A2,Coors2!A2:D3765,3,FALSE),IFERROR(VLOOKUP(A2,'AB2'!A2:C13944,3,FALSE),(VLOOKUP(A2 ,'WM2'!A2:C4843,3,FALSE))))
Then for instance at line 59 the formula is
=IFERROR(VLOOKUP(A59,Coors2!A59:D3822,3,FALSE),IFERROR(VLOOKUP(A59,'AB2'!A59:C14001,3,FALSE),(VLOOKU P(A59,'WM2'!A59:C4900,3,FALSE))))
View 2 Replies
View Related
Jul 8, 2009
I'm working with several columns of data that represent the types of employees we have at my organization. I'm trying to count the number of each type by status, as well as determine the salary payments to each type.
My problem is that we have titles like "PAA I" "PAA II" and "PAA III" and I've not been able to quite figure out the right formula to deal with this problem.
View 2 Replies
View Related
Jul 10, 2009
=SUMPRODUCT(--('Trial Balance'!A1:A100="6120"),--(ISNUMBER(MATCH('Trial Balance'!B1:B100,{"00.00.01","00.01.01"},0))),'Trial Balance'!G1:G100)
Is there a way to use this formula, but instead of looking in Column B, and having to list 00.00.01, 00.01.01, that it only looks at the last two digits of the ##.##.##.
Here is the issue. Each month, the numbers that make up what I need to add together will change. However, all I am really doing, is combining any accounts that have the same sub account (Column B) ending in the same digits of either 00, 01, 02, or 08. So I could have 01.01.01, 01.00.01, 01.02.01, etc. and I am just focusing on the .01 on the end. I would like to say, look at all the sub accounts in B, and only add (G:G) on those that the last two digits equal .01.
View 2 Replies
View Related
Jan 17, 2013
I'm trying to use wildcards in the Search formula and it isn't working the way I thought it would. I'm trying to use the following two strings:
????1234
compared to
1234????
Shouldn't search find these two strings the same since given the wildcards? Or am I missing something? If I use the above, I receive a VALUE error rather than a match.
View 9 Replies
View Related
Jan 8, 2010
I have to write some VLOOKUP formulas to pull several items from a report, the problem is there could be multiple items for each lookup value as the example below:
Company Data ABC Monday ABC Friday ABC Thursday DEF Friday GHI Monday
I need to reference every piece of data for every company but using the VLOOKUP formula, I am obviously only able to get the first instance of the lookup value (company ABC)
How to I write the formula to lookup the other data? The practical appliation I am using this for has anywhere from 300 to 400 lookup values.
View 9 Replies
View Related
Jan 18, 2010
On the uploaded workbook, Daily Nutrition Worksheet. How do I clear all the data to start fresh without deleting all the formula?
View 2 Replies
View Related
Oct 20, 2008
I have a list of account balances in one column. How can I pick up only the bottom number in that column? I have 20 worksheets and want a total across all worksheets of the last number listed in a particular column but each worksheet is different because they contain a different number of rows with activity. So, if in col. C I have:
$482
$1,000
$899
$456
$231
... then I want $231 to be what is captured for that worksheet, but if the 2nd worksheet contains:
$500
$1000
$344
... then I want $344 to be what is captured for worksheet 2. Does that make sense. In the end I want to sum $231, $344.... etc.
View 3 Replies
View Related
Feb 9, 2010
I have a list of airport codes and I need to look up these 2 digit codes and find the relevant airport and country.
For some reason when I use the lookup function it is finding the correct lookup value but returning the column cell in the previous row.
View 9 Replies
View Related
Apr 28, 2007
I have a lookup table which needs to be able to retrieve data from columns as well as rows and not sure if this is possible.
My data is set up with an ID number, financial year and data across the columns e.g
ID F/Y Apples Pears
9999 2004/5 5 4
9999 2005/6 7 8
9881 2003/4 5 3
My lookup table will have the ID number field which will be manually entered to lookup the information on the data sheet. e.g
ID 9999
FY Apples Pears
2004/5 5 4
2005/6 7 8
I can't work out a way to get excel to only give me the number of apples and pears for that year for that ID number. Not all ID numbers will have the same number of financial years so I may need to use VBA.
View 7 Replies
View Related
Dec 15, 2006
I need to match up two columns of data. I need to match the first column with text in the second column that is "like" the first. Example..
Column A
111
Column B
000111222
Basically I need to search on only part of the text in column B. So I would need to wild card both sides of the text like "*111*".
View 6 Replies
View Related
Jul 25, 2014
I'm trying to pull the earliest and lasted times out of a list of time stamps. Say I have a column full of clock in and clock out times and wanted to find the first clock in time and the last clock out time (just an example). My lookup_value would be a Store number. I can't post it here due to legality reasons, but I have a column a store numbers that will be my lookup values and I need to find the earliest and lastest time in a list of data on another tab.
View 9 Replies
View Related
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
Mar 26, 2008
Excel offers many ways to use a key to lookup a value (VLookup, Index/Match, DGet, and the rest). What's the fastest way to perform a lookup of a small table of, say, 30 rows of key-value pairs? Theoretically, it would be most efficient to use a branch table (also known as a jump table). See the wikipedia article for branch tables: http://en.wikipedia.org/wiki/Branch_table. Does Excel/VBA have a way to create a branch table for such lookups?
View 9 Replies
View Related
Mar 20, 2008
I have data validation in column A, I want column B to be dependent on contents of column A i.e. If I enter "X" in column A then column B validation list should change. I tried using "if" but it can take only 7 criteria. I want to use more.
View 6 Replies
View Related
Aug 20, 2013
All I am doing is setting up a Data Validation list of say 10 items then in another cell I am trying to use VLookup to pull the corresponding information required for the data selected in the list selection cell but I keep getting "N/A"?
Am I right in thinking that you can't VLookup from a Data Validation list? If not how do I get round this?
View 6 Replies
View Related
May 15, 2009
I want to compare a hand entered data sheet to my system data. System data is exported in. Then that data is used to populate a pivot table. I use a lookup list that fills in matching data from my hand typed sheet. From there I want to use true/false to see if the data matches. However, the formula will not copy down. I am sure it has to do with the formula holding a value, but I can’t seem to correct it.
Recap
1.System data populates sheet
2.Pivot table pulls info I need.
3.Lookup list cross references hand entered data sheet and puts value next to pivot table.
4.I need to do a true false to see if data matches but can’t get it to work
Example worksheet: On the worksheet you can see that the true false statements are incorrect. The system data and the hand entered data match two times not once.
View 3 Replies
View Related
Mar 5, 2009
I have a list of names that I'm looking up, and I want to list the data relevant to each name in a seperate place using a VLOOKUP ,(maybe theres a better way to do it) but some of the names appear more than once, resulting in an error.
I have on the first sheet, Name, Customer, Amount and Variance.
On the seperate sheet (in the same workbook) I have then name with 4 VLOOKUPS underneath, trying to extract the data Customer, Amount and Variance. But, I get errors in all of them.
Is there anyway I can List all the data relevant to each name using a Lookup function or is there another formula I should be using?
View 4 Replies
View Related
Nov 14, 2008
i have been looking around here and it seems like my problem is similar to many's regarding the vlookup function. to me, what i'm trying to do sounds simple enough, but it can't get it to work. i have a table that has three columns, Item Number, Item Description, and Amount.
Each item has it's own number, a corresponding item description, and ammount (obviously), but there are some repeats. What I want to do it input the Item Description, and have one field automatically pull up the Item number, and the next field, pull up the price. I am using a drop-down list for the Item Descriptions utilizing data validation. here is formula that i have been trying to use that is not working: =VLOOKUP(B2,Sheet1!$A$2:$C$18,1,FALSE). i have attached a trial worksheet that i am using to work things out on.
View 5 Replies
View Related
Feb 28, 2007
Excel 2003. When the Vlookup List becomes impractably long to select data from the cell dropdown. Is there an alternative prefered method to entering data from the long list?
View 7 Replies
View Related
Apr 27, 2009
I want to be able to lookup if anywhere in a cell contains a word from a list of words, and then provides an output.
Column G:
VAT payment
HMRC payment
Pay VAT
I have a table on the side that shows:
Column Y Column Z
VATHMRC
HMRC HMRC
ie. If anything in column G matches one of the words in Column Y, then output the Column Z. I have use a Vlookup that works for the first two, as VAT is the first thing, but dont know how to make it work if the key word is in the middle of the cell.
View 3 Replies
View Related
May 20, 2009
I have a range called List that has 3 columns of data:
Corporate Human Resources Services PMA
Corporate Procurement Director
Ops Central Customer Services Lost Property Office
Ops Travel Information Travel Info Call Ctr
Ops Travel Information Centres Travel Info Centres
I have 3 columns on the entry sheet called Business Unit (Col A), Department(Col B) and Sub-Department (Col C). I want to limit the choice in column B to based on the selection in Column A in the form of a drop-down list e.g choosing Corporate gives a list of Human Resources Services; Procurement.
I am using the list option for field validation and the following formula in cell B2: =INDIRECT(VLOOKUP(A2,List,2,0)). I'm not sure what I'm doing wrong as I don't get a list of options just a blank cell. Do I need to use a different formula other than VLOOKUP..?
View 3 Replies
View Related
Jun 3, 2014
I have a list of active accounts with its relevant data on one sheet, then I have a list of closed/inactive accounts that will be updated periodically by different users. I would like to run a query for the accounts on the closed list to pull in relevant data from the active list,if any, or maybe just highlight the closed accounts on the active list to ensure that the closed accounts aren't active again. Is that possible with a macro? Should it just be a vlookup? The accounts list is going to be a very big file and I am trying to find a solution that won't take up too much time to run.
View 4 Replies
View Related
Dec 12, 2011
I've found a nice looking formula for looking up multiple values from a Data Validation List which you can find here.
Unfortunately I dont know why im getting a #REF! error for one of my spreadsheets. Here is my Formula {=SUM(VLOOKUP(A2,D:D,{6,7,8},0))}
A2 is the Data Validation drop down list that has most of the Values (Letters & Numbers) I want to look up. D:D is where it will find the Values (Letters & Numbers) and {6,7,8} is the currency I want added up and displayed.
I've tried this formula on other spreadsheets with success, but no success with the spreadsheet im working on currently.
View 3 Replies
View Related
Jun 19, 2014
Attached is example of what I'm trying to do. I want to use the drop-down on the "Master Tab" and have the corresponding price by promo_month appear in the yellow cells.
Currently its on July, but I would like to switch that to August etc and have new prices populate in the yellow cells. Prices come from the "table" tab.
Example 06.19.14.xlsx‎
View 2 Replies
View Related
