July 7, 2023
This query is for a very useful formula i found in this blog.
It helps search in a cell for a list of words.
There are multiple matches in cell A2 from the list of words in H. Please help me understand how to solve this.
I have attached a sample spread sheet as well. Many Thanks :).
January 31, 2022
The article you refer to is from 2013 and works only when you want to search and return one word. In your example in A2 you have both the 5th and 6th word from the list present. SUMPRODUCT turns that into the number 11. Hence, trying to return the 11th element in a list with only 6 elements returns #VALUE!
In case you have a modern version (Excel 365 or 2021) have a look at the attached file for a possible solution.