Tabled Lookup: Able To Lookup If Anywhere In A Cell Contains A Word From A List Of Words, And Then Provides An Output
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
ADVERTISEMENT
Jan 27, 2012
i have list in A2:A4 (description) and B2:B4 is the Group of.
now i want B2:B4 fill using E2:G2 (group list which is Animal, transportation and fruit) if one of the key word in E3:G5 found in A2:A4
A2: people like to eat apple
A3: car is very expensive
A4 : dog is human best friend
E2: Animal, F2 : transportation, G2: Fruit
E3:E5 = dog, cat, horse
F3:F5 = train, ship, car
G3:G5 = apple, banana, watermelon
result i want is :
B2 :Fruit,
B3 : transportation
and B4 is animal
View 3 Replies
View Related
May 29, 2009
We have a list of typed information (see attached) in which we would like to remove some words and simply replace with a space "". In addition to this: Unfortunately, folks tend to mistype or misspell words, add extra digits, etc, and for those mistakes we would like to replace them with corrections. Therefore, for this problem, we have a Replacement Table and the Subject Data, an example of which is shown in the attachment. I have posted a slightly similar problem on Ozgrid recently:
Replace Words In Cell Matching Those In A List
and in that link, the following UDF was proposed:
Function ReplaceIfInList(rList As Range, rCell As Range, _
strReplacement As String) As String
Dim rLoop As Range
If rCell = "" Then Exit Function
For Each rLoop In rList
If ReplaceIfInList = vbNullString Then
ReplaceIfInList = Replace(rCell, rLoop, strReplacement)
Else
ReplaceIfInList = Replace(ReplaceIfInList, rLoop, strReplacement)
End If
Next rLoop
End Function
This worked great, but at the time i posted this, I did not have the foresight to anticipate the flexibility in what the replacement text would be (the above code always replaces with a ""). THIS new Post is asking the following:
Is there a way to define (or modify the above) User Defined Formula that will analyze the words typed in a cell, which are typically separated by spaces (or on accident, multiple spaces), and essentially use the Replacement Table as a reference for what word to replace with?
View 5 Replies
View Related
Mar 3, 2009
I'm trying to create a lookup formula where it matches the beginning of words and returns the value of a cell I choose. This is my layout. In Workbook1 in column E I have a list of medicine names which i'm putting in from personal medical lists. These names are not "complete", i.e. they aren't the exact names of the medicines. In workbook 2, I have a sheet with the exact names and corresponding codes of the medicines. I want to return the code of the medicine. The beginning of the names of the medicines are the same, so I want to lookup the the X first letters of the medicines I write in manually (Workbook1 column E) and find it in Workbook 2 column B and return value of Workbook2 column A for that medicine (on the same row).
View 5 Replies
View Related
Dec 25, 2012
Writing a formule to lookup for a value and return more than one result,
Example
Col A has Names, Col B has ID number, in Col C I need the output to look for names in col A and return the ID from Col B, I can use a VLOOKUP for this but the problem when col A has a name repeating more that once, In this serinario the lookup picks the first name and gives the first ID
Here is how I want the out put to be as:
Names ID Output
John 1 1,3,6
Mathew 2 2
John 3 1,3,6
Mike 4 4
Bob 5 5
John 6 1,3,6
The output should have the ID of all same names
View 7 Replies
View Related
Jun 9, 2014
I have attached a worksheet which I need to show the last update and last date customer contacted, The formula :
=LOOKUP(2,1/($F2=Data!$D$1:$D$10000)/(Data!$B$1:$B$10000=MAX(IF(($F3=Data!$D$1:$D$10000)*(Data!$M$1:$M$10000=COUNTIF($F$2:$F3,$F3)),Data!$B$1:$B$10000))),Data!B$1:B$10000)
is used to output the last contact and the formula contained within column Av, Both of which contain #N/A
View 4 Replies
View Related
Dec 1, 2007
I created a Vlookup table so that when you type a ticker into Cell B3, it tells the user how many points and what percentage of the criteria the stock passes. I would like to create a macro (or something) that takes each ticker in the Ticker sheet, enters it in the Cell B3, and then transfers the data that the vlookup table produces to the output sheet. My attachment is a simplified version of the real spreadsheet.
I hope this isn't a duplicate question. I did look, but maybe I'm not phrasing it correctly. A reference to a similar problem works too.
View 3 Replies
View Related
Dec 30, 2008
I'm having a slight problem with lookup. I have attached the workbook. On the worksheet "Time List", it shows each round what time each team plays in order of time. On worksheet "Ind. Times" it is supposed to list the game times in order of team rather than times.
I'm using lookup, but the values in "Ind Times" are incorrect. If you look at the very first value... It says Monday Night Roast have a BYE Round 1, but looking at "Time List" you see they actually play at 8.00pm. On a side note, if I change the values of the team names to numbers - it works no problem at all.
View 3 Replies
View Related
May 1, 2013
I am struggling to begin designing a program to use for quoting sheet metal parts. We buy three types of material in 9 different thickness. The material is purchased by weight but I sell it by square foot.
I tried a few training exercises using macros but I dont believe macros is the proper function to achieve my output.
View 6 Replies
View Related
Apr 3, 2013
language
batch
WC
FC
zh
1
36
0
id
1
0
0
[code]....
in the above table I need to the sum of WC for each Language code & batch no. eg. for Language "id" & batch 1 I need to get the SUM of WC corresponding to the criteria.
View 2 Replies
View Related
Jan 2, 2009
I have a workbook with 2 different types of sheet - 1 containing source data and the others 'collecting' data from the source sheet, depending on what the sheet is for.
For example, the data source contains different pets, their names, ages and their owners.
The other sheets are on a one-per-owner basis.
What I would like to do is use a LOOKUP / MATCH function to lookup the owner name typed in cell A1 of the output sheet and match it with the corresponding owner name(s) on the source sheet. I would then like it to return with each pet and append the results on the sheet accordingly - like below:
John Smith (in cell A1)
Pet - Name - Age
-------------------
Dog - Rover - 3
Goldfish - Tom - 1
Gerbil - Chewit - 4
View 7 Replies
View Related
Apr 29, 2014
I have two sources of information that I need to look up on, but one of the sources contains other words within the same cell?
For Example I have a source of data which contains all our client names, and I need to search for the client name in a second source of data to bring through the value from another cell, however the table array that I would do the look up on contains both the client name and project name together in one cell - therefore I am unable to do a normal Vlookup - also the client name is not always the same number of letters.
See an example:
Source data 1:
Client name:
XYZ Ltd
Source data 2:
in cell A1 XYZ Ltd/project ABC
in cell A2 shows the value e.g.: £5000
Would there be anyway to do a look up on XYZ Ltd to bring through the value in A2 on source data 1?
View 5 Replies
View Related
Jul 31, 2008
I have an issue with VLOOKUP. By runnig VLOOup we can get the data that there is present in a specified range. But how about to get the data DISPLAYED which is not there in the specified range. I tried combining IF and VLOOKUP functions. I am on a simple project now and I would be happy if I could get the answer immeidately
View 2 Replies
View Related
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
Jan 20, 2010
I have been playing around with this for a bit and now have it sorted out. This is a macro that will check a name list and highlight any cell that contains one or more of the names.
View 3 Replies
View Related
Aug 6, 2009
I am trying to find a solution to a vlookup issue. I have attached to this post a sample excel file which will make it easier to understand. I have data in one table (Table 1) where the records consist of addresses with street field, city and zip. These records have to be flagged based on the street address (number and street name provided the city or zip is the same) with a flag called "Type"(as an example Type 1 or type 2, etc).
Than I have another table (Table 2 or the lookup table) that has unique adrresses with the "Type" field that needs to be brought over to Table 1. The issue that I run into is that addresses in Table 1 have variations such as 25 Main Street can be as 25 Main St., or 25 Main St, or 25 Main st., Apt 2, which all should be recognized as 25 Main Street. All we care is to flag 25 Main Street with it's appropriate flag from Table 2.
Data in Table 1 can be in the 100k or more records in some cases so any manual intervention would be very time consuming and not efficient. Now I've tried doing lookup only on the 3 or 4 left characters of this field but than you get thrown a curveball when the address is for example 25 Maitland Avenue, which will falsly flag them as 25 Main Street. Plus house numbers can be 25 Main, or 255 main 2554 Main or 2 Main, so it is not functional to use the left function nested within vlookup. This issue seems to be more of a logical validation, which I don't know how to approach. The best way to think about this is when you go print a priority label on the USPS website and they correctly validate your address eventhough you might have mistyped your address.
View 2 Replies
View Related
Apr 7, 2014
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
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
Jun 12, 2009
I am trying to perform a lookup (vlookup) function in a cell in excel and wish to have the range as a variable, so that I can adjust which column the lookup function refers to.
View 4 Replies
View Related
Jan 26, 2010
I'm making my own gradebook (attached) and one of my sheets will list scores for each student in different assignments. I have one sheet which keeps track of all students and all assignments with other info. I would like to program cells in one sheet (the third in the attached file) to lookup a particular student's grade in a particular assignment. I figured trying a LOOKUP with an AND requirement might work but it keeps returning the message "could not find value".
My formula references the student's name and the assignment from the identifying cells so that it is easy to copy and paste. I wondered if it was this which resulted in the error, but doubt it.
View 4 Replies
View Related
Jan 21, 2010
On sheet 2 in cell C5 I need it to find it from sheet 1 referring it from the word "Drinks 1" thats in Q1 and so on along the row
View 9 Replies
View Related
Mar 24, 2008
I have a column of address data that looks like
1924 ogden place blvd west unrulu
982 n aoto st apt #1234 easrlr
...
and I'd like to use a couple of formulas in the two columns to the right of this data
1st column:a formula to obtain the number of words in a cell
2nd column:a formula to extract the last n words from a cell (ex. the last 2 words)
so that the columns would have as their results
6 west unrulu
7 #1234 easrlr
View 9 Replies
View Related
Jul 29, 2008
I am trying to use lookup function to lookup for data in another table (we call it table A). Unfortunately, whenever the code is not in the table A, Excel will return the data from the previous row.... is there any possible way to prevent this... in another word, if the code does not exist in the table A, I want Excel to return 0 or some other figures.
View 9 Replies
View Related
Nov 28, 2006
here is an example....
(this is on a sheet called Summary)
----A--------B --------C------- D
1Names----At Bats----Hits----Batting Average
2Tom-------38--------31------.816
3Derek------19--------14------.737
4Joey-------40--------28------.700
5Chris-------40--------27------.675
6Chuck------37--------24------.649
Using the LARGE function, Excel has created a list based on batting averages(on a separate sheet called Line-up). It looks like this.....
---A----------B
1Names----Averages
2 -----------.737
3 -----------.700
4 -----------.675
5 -----------.816
6 -----------.649
The problem I am having is figuring a way for Excel to also bring the corresponding names (after using the LARGE function to create the line up list).
View 3 Replies
View Related
Jun 12, 2007
I have a very large spreadsheet of customer information(I call it the master spreadsheet). Each row contains only 3 things: Account number, product bought, Price
Later I receive the money from the customer for that product(the pay sheet) that contains the exact same thing in the same order: Account number, Product bought, Price paid.
What I'm trying to do is compare the two spreadsheets so that when i receive the pay sheet of cusomters who have paid with the amount it will deduct it from the master sheet.
So it should compare account numbers when it finds a match then it should subtract the amount paid (column C) from the master spreadheet price column(column C also).
sometimes customers don't pay the right price so it has to be a subtraction so I can see if it was over paied, underpaid etc.
Right now I'm still doing it manually combining the two documents sorting it by account number and checking for matches in column A (account number).
View 5 Replies
View Related
Jun 9, 2014
So I have a cell with multiple words in A1. I want to check for the word "red" in A1 and if red exists return a value of "red" in B2, IF "red" is not found then search for "blue", if "blue" exists return a value of "blue" in B2, if "blue" is not found is not found then search for "green", if "green" exists then return "green" in B2, if none exist then return value of "none"
View 14 Replies
View Related
May 27, 2014
Looking to find 1 of 2 words in a cell in column B and return the word found in the same row in column E. This seemed easy but I am not having any luck.
the cells in column B have several words in them but I am looking for 2 specific words "PLAT" and "ORIG". If the word is not in the cell, it should show a blank cell in column E in the same row, otherwise one of the 2 words should be in that row in column E. A VBA loop would be ideal but a formula that can do it might work as well.
View 3 Replies
View Related
Jul 15, 2009
In Column A i have a list of race meetings. Column B is the Country.
Column D - List of Tracks
Column E - Country Code the Track belong to
So what i like to do this this, i need to find out which Country a particlar Race Meeting belongs do using the Track List.
E.g. A2 - Ayr
In my Track List ayr = UK
So i need UK to be displayed on my B2 cell to state its from UK.
View 4 Replies
View Related
Jan 18, 2012
I have 3 sheets.
Sheet 1: A complete data set:Sheet 2: A partial data set (from sheet 1)Sheet 3: Empty tableI want to pull data from sheet 1 if it 'isn't' in sheet 2. Is there a way of tweaking vlookup for this purpose?
View 2 Replies
View Related
Feb 21, 2012
I have two lists.
The first:
P1116
MC1070
BD1068
I want to check if any of these values are contained in the text of each item in the second list, that looks like this:
64K4014-4" , 64K1030-4"
64P1116-3"
64P1187-4" , 64p1117-4"
64P1330-2"
64P1405-2"
64MS1007-0.75", 64MC1070-2"
64K1062-3/4"
64K1061-0.75"
64CW1062-2", 64BD1652-2"
64NG1007-1 1/2"
64K1008-2", 64BD1041-1", 64BD1068-2"
64HS1002-6"
64HS1002-6"
64CW1068-2"
I tried using the =search() function in an array formula, but I can only get it to work for a single value in the first list.
View 8 Replies
View Related
