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 in Others by Kithuzzz
• 20,660 points
75 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 by narikkadan
• 37,660 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 in Others by gaurav
• 22,040 points
76 views
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer

How to apply zoom animation for each element of a list in angular?

Hey @Sid, do check if this link ...READ MORE

answered Jul 30, 2019 in Others by Vardhan
• 13,200 points
615 views
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 in Others by narikkadan
• 37,660 points
41 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
321 views
0 votes
1 answer
0 votes
1 answer

Spark Kill Running Application

you can copy the application id from ...READ MORE

answered Apr 25, 2018 in Apache Spark by kurt_cobain
• 9,390 points
1,063 views
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 in Others by narikkadan
• 37,660 points
128 views
0 votes
1 answer

On focus after tabbing of Excel drop down, automatically show list for selection

Put the following code in the Microsoft ...READ MORE

answered Oct 3 in Others by narikkadan
• 37,660 points
32 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