Excel Search for a list of strings within a particular string using array formulas

0 votes

I want to search a cell for a list of words. I thought this would work as an array formula:

{=FIND(<list of words I want to search for>,<cell I want to search>)}

However, it only recognizes a match when the word I'm looking for appears in the first row in the list of terms I'm looking for in the cell I'm searching. Is it possible to create a formula that iterates over the full list? And I would prefer that it return more than just TRUE or FALSE. I am aware of how to perform a cell search for a list of terms and output TRUE or FALSE depending on whether or not each word is present in the cell. I want to know the precise word that was found or its location.

Oct 3, 2022 in Others by Kithuzzz
• 28,520 points
286 views

1 answer to this question.

0 votes

If a match is detected, this will return the word, otherwise, it will return an error. The following is what I used for this example.

List of words to search for: G1:G7
Cell to search in A1

=INDEX(G1:G7,MAX(IF(ISERROR(FIND(G1:G7,A1)),-1,1)*(ROW(G1:G7)-ROW(G1)+1)))

Enter as an array formula by pressing Ctrl+Shift+Enter.

This formula records the position of the word in the list as a positive value if it is found or as a negative value if it is not found by first searching the list of words for matches. The position of the discovered word in the list is represented by the greatest value in this array. A negative value is supplied to the INDEX() method and an error is raised if no word is found.

To return the row number of a matching word, you can use the following:

=MAX(IF(ISERROR(FIND(G1:G7,A1)),-1,1)*ROW(G1:G7))

This also must be entered as an array formula by pressing Ctrl+Shift+Enter. It will return -1 if no match is found.

answered Oct 3, 2022 by narikkadan
• 52,760 points

Related Questions In Others

0 votes
1 answer

Generate a flat list of all excel cell formulas

Hello, you'll have to follow certain steps ...READ MORE

answered Feb 18, 2022 in Others by gaurav
• 22,940 points
114 views
+1 vote
1 answer

Excel or Google formula to count occurrences of an 8-digit number within a text string

To match an eight-digit number, you may ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 52,760 points
69 views
0 votes
1 answer

How can I perform a reverse string search in Excel without using VBA?

This one is tested and does work ...READ MORE

answered Jan 5 in Others by narikkadan
• 52,760 points
58 views
0 votes
1 answer
0 votes
1 answer

Multiple find and replace in MS Word from a list in MS Excel

If I understand you correctly, you want ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 52,760 points
86 views
0 votes
1 answer

Excel VBA - Subscript Out of Range Error (Run Time: Error 9)

Set rangePartner = Range(Partner.Cells(2, 2), Partner.Cells(2, 2).End(xlDown)) Needs ...READ MORE

answered Jan 21 in Others by narikkadan
• 52,760 points
44 views
0 votes
1 answer

Examples for string find in Python

you can use str.index too: >>> 'sdfasdf'.index('cc') Traceback ...READ MORE

answered Aug 29, 2018 in Python by Priyaj
• 58,100 points
358 views
0 votes
1 answer
0 votes
1 answer

Get number of columns of a particular row in given excel using Java

Use: int noOfColumns = sh.getRow(0).getPhysicalNumberOfCells(); Or int noOfColumns = sh.getRow(0).getLastCellNum(); There ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 52,760 points
487 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP