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

by

This will return the matching word or an error if no match is found. For this example I used the following.

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 works by first looking through the list of words to find matches, then recording 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. The largest value from this array is the position of the found word in the list. If no word is found, a negative value is passed into the INDEX() function, throwing an error.

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.

More Related Contents:

  1. Want to concatenate multiple row data in in cell along with its values if present. Emp Id should not come if no data available infromt of it
  2. Calculation is wrong, Rounding wrong
  3. Excel formula checklist in employees
  4. Sequence of numbers based on start and end value of 2 cells [closed]
  5. Excel VBA – How to add dynamic array formula
  6. Return empty cell from formula in Excel
  7. TEXTJOIN for xl2010/xl2013 with criteria
  8. Difference between date and time w/out work week Excel
  9. How can I perform a reverse string search in Excel without using VBA?
  10. Ignore Duplicates and Create New List of Unique Values in Excel
  11. Excel Formula for Inventory
  12. Count Unique values with a condition
  13. Using UNIQUE with non-adjecent columns on different sheets
  14. Count unique values in Excel
  15. Concatenate column headers if value in rows below is non-blank
  16. If two cells match, return value from third
  17. How do I apply a SumIf Formula in a VBA Sub? Getting Error 1004
  18. Using the value in a cell as a cell reference in a formula?
  19. How to list all possible combinations of the values in three columns in excel?
  20. Excel – extracting top 5 values
  21. How to split texts from dynamic range?
  22. How to count cells in a range with a value less than another cell in excel?
  23. Extract the last substring from a cell
  24. How to extract text within a string of text
  25. Excel: How to gather unique values in one column that are associated with duplicates in another column?
  26. excel vlookup with multiple results
  27. How to roundup a number to the closest ten?
  28. Convert an Array Formula’s Text Results into a Usable Format
  29. Can’t pass a range to a lambda using Let and Makearray functions
  30. Unique Rank value for a subgroup within a group

Leave a Comment