Lookup By Partial Text Match
Apr 9, 2008
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 3 Replies
ADVERTISEMENT
May 20, 2014
This is for Excel 2007,I have two sets of model numbers. One set is the full model numbers of the units we use, and the other is an abbreviated form used to lookup up certificate numbers. I need a way to match these up so I can use one set of search criteria to find out if there is a match. Here is an example of what I need to match with a partial text match:
H,AE35(6,9)36+TD and AE3563636D145C2501AP
H,RE36(6,9)36 and RE36936C145B2505AP
if I could do this with a formula that matches multiple items at the same time (ie; if A & B & C match=true) with the above model number being one of those items (certificate numbers are issued for sets, but the other model numbers are fine).If that's not possible, a one time VBA run to match all of the abbreviations at once would also work. If these items are matched up with a one time VBA, the VBA needs to account for their being more than one match for each abbreviation depending on the size of the unit.
So H,AE35(6,9)36+TD could be matched to:
AE3563636D145C2501AP
AE3563636D175C2501AP
AE3563636D210C2501AP
View 11 Replies
View Related
Dec 14, 2012
I am trying to create a lookup table that will lookup parts of a colum and return the second column in the table. For example, column A has a lot of different titles in it, they each contain different segments and codes in them but I need to see if NM is in the list and if it is I want to return a 1.
Column A Lookup COLUMN Lookup Result
NM01A NM 01
NM02B IN 02
IN01A
IN02B
I need to see if what is in the lookup column is in column A, and if it is then I need to return the lookup result.
View 6 Replies
View Related
May 2, 2014
i need some formula to lookup with partial match "name", like this below :
name
property code
partial name (manual type name)
desired result
jim bob
1988.08.000250
jimmy bob
1988.08.000250
[Code]...
etc...
it's possible with excel formula?
View 3 Replies
View Related
Sep 4, 2006
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 3 Replies
View Related
May 2, 2008
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 3 Replies
View Related
Mar 15, 2007
Cell H1 has a variable string of references for eg
"FI570783AQ3516346EQ3516346FXVB123456"
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 9 Replies
View Related
Jul 11, 2014
How to check for a partial match looking in one cell, A1 for example, and checking to see if the contents of B1 show a partial match (example attached).
I can't use the vlookup because I only want to look at cells specifically, not a table/list. Please also note that the attached example has no formulas.
Forum Example.xlsx
View 5 Replies
View Related
Mar 28, 2013
I have a long list of categories in the column A (seen below). To this list i want to assign keys that will support with further calculations (in a column B).
An example of such a key would be in the case of the given example "ACTION SPORTS" and "FOOTBALL". The whole list of these "keys" hast 17 elements.
It is evident that i will need some kind of a partial match. The one i have found on the internet, limittin itself just on the first or last number of characters wont work.
Would it be possible to select my "key - list" (all 17 of them). Then excel would asign a key, whenver it would find a partial match, matching the key?
Appendix:
The Data list (Column A):
ACTION SPORTS FTW
ACTION SPORTS FTW
ACTION SPORTS FTW
ACTION SPORTS FTW
ACTION SPORTS FTW
[code].....
View 6 Replies
View Related
Jun 3, 2014
I'm working on a formula to make it enable a part of the text then return the best possible match. Below is my formula
=MATCH("*"&$A11&"*",'[Customer Master List - 05.30.xlsx]Export Worksheet'!$B$82:$B$1298,0)
However, it works with some text but won't work for some.
For example, I have this text CARE-A-LOT, INC and in the master sheet there is a similar text like this CARE-A-LOT. I want it to return CARE-A-LOT as this the best match possible.
View 2 Replies
View Related
Feb 20, 2014
I'm looking to use Excel to finding partial text In the case of more than one match
I used the following formula in cell C12:
=IFERROR(INDEX($C$2:$C$8, SMALL(IF($B12=$B$2:$B$8, ROW($B$2:$B$8)-MIN(ROW($B$2:$B$8))+1, ""),COLUMNS($B12:B12))),"")
But it's work only 100% match word not by partial text.
View 2 Replies
View Related
Jan 17, 2013
I do PPC work and you can export query data from Adwords with Column A being the actual search query. Then column B - E are impressions, clicks, cost & conversions (from each query). In this example, cell A1 might be running shoes, cell A2 might be walking shoes, cell A3 is running pants, and cell A4 is baseball pants. I want to add a new column (F) that does product category groupings based on partial text matches in column A. For example, anything that includes the text string shoe or shoes should be labeled as shoes in column F. Similarly, all queries that contain the characters pant would be listed as product category pants in column F.
Currently I do this manually by conditional formatting on column A for contains text and then color the cells. Then I sort by cell colors. Then I manually type in the product category into F for each color block in A. Needless to say, this is slow and manual.
What I would prefer is to have a master table (like a vlookup) on sheet 2 where column A is the list of partial text matches and column B is the product category to be returned if that the partial text in sheet 2 column A is found in the query list on sheet 1 column A. This way, on sheet 2 I could have cell A1 sneaker, cell A2 shoe, cell B1 shoes, cell B2 shoes, etc to manage the correlation between text strings and product category groupings.
Basically I want a formula to put in the cells on sheet 1 in column F that searches all of column A on sheet 1, looking for partial text matches from column A on sheet 2 and returning corresponding product category in column B on sheet 2.
View 6 Replies
View Related
Jun 19, 2014
I need a formula to do a partial text match on column B to find all rows that contain "825-CL-A", then sum column C for all applicable rows with the latest date. In this example the result should be "4.25 + 6.50 = 10.75". I'm using Excel 2003 for this project.
A B C
7/1/2012 0:00825-CL-A-41091-REG4.00
7/1/2012 0:00825-CL-A-41091-REG6.25
7/1/2013 0:00825-CL-A-41456-REG4.25
7/1/2013 0:00825-CL-A-41456-REG6.50
1/1/2014 0:00825-CL-A-41640-REG4.25
1/1/2014 0:00825-CL-A-41640-REG6.50
3/1/2014 0:00825-CL-E-41699-REG3.00
3/1/2014 0:00825-CL-E-41699-REG4.00
View 14 Replies
View Related
Sep 21, 2013
I have a WB with multiple sheets. I have a Feed sheet that automatically pulls data from the web. I have a Scores sheet that currently has all head to head matchups each week for an entire season. So I want to check the cells on the Feed! sheet to find a partial match and if partial match exists then copy cell that contents score from feed sheet to the score sheet next to the appropriate teams name. Currently I have to manually enter all scores each week for the rest of my WB to update.
I need to look at Cell A3 (on the Feed sheet) which has "New York Jets" then search for a partial/similar match on the score Sheet (which is NY JETS, in this case). NY JETS could be in column B (rangeB2:B257) OR column D (rangeD2:D257) BUT I need to search by row, not column, then once a match is found check the cell to the immediate right and only if the cell is blank copy data from the (!feed) sheet to that blank cell on the (!scores) sheet
This is what happens if working right =
look at cell A3 on the (!feed) sheet = "New York Jets" then search (!scores) sheet Column B and Column D by row for a partial match, finds "NY JETS" as match in cell D8, if cell E8 is blank then copies cell H3 from (!feed) sheet, and pastes to cell E8 on the (!scores) sheet, if cell is not blank continues search until 1st blank cell to the right of matching cell is found (as there will be mutiple matching cells with blank cells to the right but I am only interested in the 1st blank cell found, once found and data copied the process is done and then starts over with cell A4)
Look at cell A4 (!feed)= "New England Patriots" then search (!scores) sheet Column B and Column D by row, finds "NEW ENGLAND" as match in cell B8, if cell C8 is blank then copies cell H4 (!feed) sheet, and paste to cell C8 (!scores)
Once this is done it moves on to the next cell in the next row on the (!feed) sheet, A5, to find a partial match for the data in that cell. I need to continue the search for each cell A3 to A74, and if no match is found to move on to the next cell A6... (based on the way the data is pulled in from the web there are some blank cells as well as some cells that say Game Final, this data won't be on the scores sheets in column B or D).
View 1 Replies
View Related
Sep 9, 2013
I saw a post with a formula of =LOOKUP(9.99999999999999E+307,SEARCH(" "&KeyWords&" "," "&$A1&" "),KeyWords) which I thought might work, but it returns an error.
I'm trying to search on A1 and return the correct name from a named range called KeyWords.
A
B
C
D
1
Chicago
#N/A
Chicago IAP
[Code] ......
View 4 Replies
View Related
Aug 25, 2013
I am doing the partial lookup to get the total amount based on a certain criteria.
For example in sheet1 there is a table and on sheet2 I have to pull out the data from sheet1.
Column A
Column B
Provision of Maisie System User -
56988.89
Provision of Maisie System User - first 12 free
7451.78
Provision of Maisie System User - first 12 free (rebate 1 May 08)
-487.25
[code]....
The formulas which i m using is =VLOOKUP("*"&$A$2&"*",$A$2:$B$10,2,0). Its giving me the first value as there are duplicates i need the total sum. For example:- Maisie amount which i m getting is 56988.89 but i want total maisie amount which is 56501.64.
View 3 Replies
View Related
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
Nov 13, 2009
In cells A1:A10 I have the following list;
FLOUR
DOUGH
CHICKEN
BEEF
PEPPERS
TOMATOES
CHEESE
ONIONS
POTATOES
SALAMI
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 12 Replies
View Related
Nov 3, 2008
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
Testing
Reference
Example john commission
Example (hard sell)
Is there a formula to return the number of cells that have "Example" in them?
View 5 Replies
View Related
Dec 13, 2008
column A
MARY
JOHN
MARK
SUE
cell B1
zzzJOHNxx
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 9 Replies
View Related
Sep 21, 2002
How do you do a vlookup with a partial match?
View 5 Replies
View Related
Feb 14, 2012
I have one list of waybill numbers which are 12 digits long and I have another file of waybill numbers which are 15-16 digits long. I have tried a VLookup but I do not get matches on these since the digits are not the same length. Is there any other formula I can use to match a 12 digit waybill against a 15-16 digit waybill number? I am assuming that if 12 digits match in both lists that it is an exact match.
View 4 Replies
View Related
Mar 12, 2012
I have data below, what I am trying to do is to put an "x" if the data has a partial match. This is what I am using below.
HTMLSheet1 ABC1G. Washington SchoolGEORGE WASHINGTON SCHOOL 2Electrical Magnet SchoolELECTRICAL 3Sports Magnet SchoolCOLUMBUS SCHOOL 4JonesJONES SCHOOLx5J. StrongSTRONG SCHOOL 6Abe Lincoln SchoolLINCOLN SCHOOL 7HarrisonHARRISON SCHOOLx8Abe Lincoln SchoolLINCOLN SCHOOL 9Abe Lincoln SchoolLINCOLN SCHOOL 10Abe Lincoln SchoolLINCOLN SCHOOL Spreadsheet
[Code] ...........
What I would like is this
HTMLSheet1 ABC1G. Washington SchoolGEORGE WASHINGTON SCHOOLx2Electrical Magnet SchoolELECTRICALx3Sports Magnet SchoolCOLUMBUS SCHOOL 4JonesJONES SCHOOLx5J. StrongSTRONG SCHOOLx6Abe Lincoln SchoolLINCOLN SCHOOLx7HarrisonHARRISON SCHOOLx8Abe Lincoln SchoolLINCOLN SCHOOLx9Abe Lincoln SchoolLINCOLN SCHOOLx10Abe Lincoln SchoolLINCOLN SCHOOLxSpreadsheet FormulasCellFormulaC3=IFERROR(IF(SEARCH(A3,B3),"x",""),"")C7=IFERROR(IF(SEARCH(A7,B7),"x",""),"")
View 6 Replies
View Related
Jan 10, 2008
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
exemple
possible matching
28244654-500410428244654Y71J
comun value 28244654
same
123abcabcd
View 9 Replies
View Related
May 12, 2014
[Code] .....
My data are ABC:apple and im looking in a column where data are like these : apple, melon, lettuce.
View 1 Replies
View Related
Nov 24, 2009
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
=if(a1="xyz",a2,"")
here it is exact match, i need partial match, or multiple partial match in a single cell. How to do it?
View 9 Replies
View Related
Jul 19, 2013
I am using VBA
I have an input box pop up to ask the user for a base item number. The way the excel sheets part numbers work for example is TM-T88VP-GRY, the last part 'GRY' changing based on the variation of the item that someone ordered. So, the base item number would be TM-T88VP, and what I want to do is search through a set column (Column G) and delete any row that contains that base item number.
View 4 Replies
View Related
Aug 15, 2013
From the attached file.
Column 1 is a file name
Column 2 is a list of file names (but some of the files have been split into -A and -B parts or suffixed with an A or B
I need to highlight in column B, if the exact file name in column A exists
Example:
Column A: BSDS-0001
Column B: does not have this exactly instead it has BSDS-0001-A and BSDS-0001-B
I need some indication/highlighting that BSDS-0001 does exist on column B
So an indication that the whole of the file name in column A appears partially in column B. I have tried using max character lengths searches etc but cannot get an accurate way of doing this.
View 11 Replies
View Related
Aug 11, 2007
I currently have a speadsheet that looks similar to the following:
REF DATA1 DATA2
SVRW_67 784598 475395u
SVRW_34 fdsjsjdf 734978
CAT_56 ghdrhad gaghadh
CAT_67 48578 8943539
I'm trying to create a macro that will delete all rows with "CAT" in the REF column.
I've searched this site and struggled trying to adapt other methods listed and got nowhere.
View 3 Replies
View Related
Feb 19, 2008
i would like to find code for a macro(s) to delete selected columns and delete row by partial string..
due to my novice excel skills i would prefer in two separate macros so i can trigger from another macro if poss..
View 8 Replies
View Related
