Search for a text string in an excel table
Answer:
Excel 2007 array formula in B13:
Select cell range B13:E16. Click formula bar. Press + CTRL + SHIFT + ENTER.
Replace FIND function with SEARCH function if you don´t want the formula case sensitive.
Named ranges
Table1 (B3:E6)
What is named ranges?
Download excel example file
search-for-a-string-in-an-excel-table.xlsx
(Excel 2007 Workbook *.xlsx)
Explaining array formula in cell B13
Step 1 - Identify cells containg search string
Find function returns the starting point of one text string within another text string. ISNUMBER(FIND($C$9, Table1)) creates this array in cell B11:E14:
Column B has no cells containing string "AA".
Column C has 1 cell containing string "AA". Cell C3
Column D has 1 cell containing string "AA". Cell D4.
Column E has 1 cell containing string "AA". Cell E5.
Step 2 - Return row number
ROW(Table1)-MIN(ROW(Table1))+1, "") returns this array:
If string "AA" is found in a cell in the table the corresponding row number is returned.
Step 3 - Sort the row numbers from smallest to largest
Small function returns the k-th smallest number.
SMALL(IF(ISNUMBER(FIND($C$9, Table1)), ROW(Table1)-MIN(ROW(Table1))+1, ""), ROW(Table1)-MIN(ROW(Table1))+1) returns this array:
Step 4 - Return a value at the intersection of a particular row and column
INDEX(Table1, SMALL(IF(ISNUMBER(FIND($C$9, Table1)), ROW(Table1)-MIN(ROW(Table1))+1, ""), ROW(Table1)-MIN(ROW(Table1))+1), COLUMN(Table1)-MIN(COLUMN(Table1))+1)
Step 5 - Remove errors
=IFERROR(INDEX(Table1, SMALL(IF(ISNUMBER(FIND($C$9, Table1)), ROW(Table1)-MIN(ROW(Table1))+1, ""), ROW(Table1)-MIN(ROW(Table1))+1), COLUMN(Table1)-MIN(COLUMN(Table1))+1), "")
Functions in this article:
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
ROW(reference) returns the rownumber of a reference
COLUMN(reference) Returns the column number of a reference
IFERROR(value;value_if_error) Returns value_if_error if expression is an error and the value of the expression itself otherwise
MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text
FIND(find_text;within_text;[start_num]) Returns the starting position of one text string within another text string. FIND is case-sensitive
SEARCH() Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)
SMALL(array,k) returns the k-th smallest number in this data set.
This blog article is one out of five articles on the same subject.
- Search for a cell value in an excel table
- Search for a text string in an excel table
- Lookup with multiple criteria and display multiple search results using excel formula
- Lookup with multiple criteria and display multiple search results using excel formula, part 2
- Lookup with multiple criteria and display multiple search results using excel formula, part 3
Related posts:
Search for a text string and return multiple adjacent values
Search for a cell value in an excel table
Filter records within two dates and search for a text string in excel
Filter unique distinct values where adjacent cells contain search string in excel
Search a table and use the returning value to search another table
Recommended categories
Dear Oscar,
Do you know if there is a formula (or combination) that results in a text value I want to use with "vlookup"
Something like the "like" function in Access.
Example:
text = "Kn1263-Techstore-MrFixit-12-11-2011"
I want to Find the part "Tech".
Then with Vlookup I want to find a related account in an Table.
such a Tech is related to "200 Repare account"
So I can keep controle over my Bankaccount for example.
Maybe in VBA?
Thanks in advance for your reply.
Kind regards,
Lourens van 't Wout
The Netherlands
Lourens van 't Wout,
Yes, I believe it is possible.
Can you describe the two tables and how they are related?
Lourens,
This post describes how to search for multiple textstrings and return a match from another table: http://www.get-digital-help.com/2010/11/22/return-multiple-matches-with-wildcard-vlookup-in-excel/
Hi Oscar,
Request your help for a similar kind of a query. I'm in a fix and can't really figure out how to get the required output.
I have two columns A & B both containing string values. The value in Column A is basically a reference number and value in Column B contains detailed text containing that number itself...
For e.g. Row 1 for Column A has a value 00125465.. Now in column B I have a value like (without qoutes) "With reference to the record number 00125465, we would like to..". This value of column A might exist in multiple rows of Column B.
In my actual data there are around 160,000 unique values for column A and 97,000 values for column B. I need to search for all values of column A in column B and have some kind of an identifier in say Column C to know what values for column A are present in Column B. Please note that a value for Column A might exist on row # 10 and then row # 1000 in column B.
Hope you can help me with this.
Cheers !
Hasan,
Check out the attached file:
Hasan.xlsx
Hi Oscar,
Many thanks for the file. Apparently it looks to be exactly what I was looking for. This is simply great
..
Having said that the code seems to be really complex. Can I please have your email address so that I can request for any clarifications if required for the code ?
Once again thanks a lot.
God Bless !!
Hi Oscar,
Sorry for bothering you again for this. Will you be please kind enough to define the formula in two three lines in simple words for me.. Can't really understand how these functions are working.. specially ' _xlfn.IFERROR '
..
Apologies for the bother but would really appreciate your help.
God Bless !
Hasan,
You can find an explanation here:
http://www.get-digital-help.com/2010/11/22/return-multiple-matches-with-wildcard-vlookup-in-excel/
IFERROR function removes errors.
hi, i was wondering how can i make this search formula to work in excel 2003 i get an error #name! in the search result box. thanks
ali,
Array formula:
=INDEX(Table1, SMALL(IF(ISNUMBER(FIND($C$9, Table1)), ROW(Table1)-MIN(ROW(Table1))+1, ""), ROW(Table1)-MIN(ROW(Table1))+1), COLUMN(Table1)-MIN(COLUMN(Table1))+1)
search-for-a-string-in-an-excel-table.xlsx
ple seam file find transfar to sheet no 2
Hello Oscar,
If I do not want to use table in the formular for ranges in 2003?
My search is in range a1:a20 and its looking for the text in b2:b500
Array formula:
=INDEX(Table1, SMALL(IF(ISNUMBER(FIND($C$9, Table1)), ROW(Table1)-MIN(ROW(Table1))+1, ""), ROW(Table1)-MIN(ROW(Table1))+1), COLUMN(Table1)-MIN(COLUMN(Table1))+1)
James,
I believe this post answers your question:
http://www.get-digital-help.com/2010/04/07/lookup-values-in-a-range-using-two-or-more-criteria-and-return-multiple-matches-in-excel-part-2/
Array formula:
Hi Oscar,
This formula works great for what I'm trying to achieve.
I have a project data sheet capturing names of people involved. I'm using this to allow people to search for a particular name, and for all relevant projects to be displayed.
Have modified the formula to use a SEARCH function instead of FIND for the closest match, but I need to identify all the column numbers in the data matrix where there's a match for an index function against another matrix of the same size.
I've figured out how to retrieve the row numbers, but am stuck at getting the right columns to be displayed.
Wenyong,
but I need to identify all the column numbers in the data matrix where there's a match for an index function against another matrix of the same size.
Can you explain in greater detail?
Thanks Oscar, for the prompt reply.
Is it possible for me to attach or send you an example of what I'm working on?
I'll try explaining in more detail:
I have a datasheet on different projects, names of colleagues involved in the project are organized in 12 columns (each cell may contain more than one name. For this reason, I've modified your function to use SEARCH instead of FIND), and their involvement in the project captured in another array of 12 columns.
Purpose of the spreadsheet is for any user to enter a search name (e.g. Peter) and for the spreadsheet to display all projects this person is involved in. For this, the function you've developed works beautifully. This is also why I'm using SEARCH, as the user may enter only a first name, and the function can capture the closest match from a cell that may contain 3 names. FIND can only return an exact match.
Besides returning the name of the project as a result, I'm looking to develop this further by displaying the role of the person for this project. For example, Peter is involved in Project X as a Sales Manager. His name is captured in Resource_Name_Column7 and his role is captured in Role_Column7.
The current function can return Project X as a result when user searches for 'Peter'. However, I do not want to index out all 12 columns that matches the Row at which his name is found.
I'd like to display the role he plays in this project by indexing the x and y coordinates at which his name is found in the Names array, against the Roles array which is of the same size (both 12 columns).
I've manage to display and return the result for Row number of the project, but couldn't find a way to capture the column number at which his name is found
Wenyong,
You can use this contact form.
The above example is excellent one.
But my requirement is something different.
I need to print only column1 contents when the pattern AA matches.
Also if "AA" presents twice in same row this particular cloumn1
content should be repeated twice.
Thanks
senthil,
Read this: Search for a text string in a column and return multiple adjacent values corresponding to the number of matching values
Oscar - Thanks so much for providing this! It's really helpful.
I've implemented the array formula and it works great except that if the search string occurs 2x in row of data, the row is returned twice. If it occures 3x, the row is returned three times.
I'd like the results to show only 1 instance of each row no matter how many times the search string appears in that row. How can that be done?
Thx!
Richard,
See this file:
search-for-a-string-in-an-excel-table-return-unique-rows.xlsx