Log In
Register
Home
Active Member
July 7, 2023
Offline
Hello Everyone,
This query is for a very useful formula i found in this blog.
It helps search in a cell for a list of words.
https://www.myonlinetraininghu.....t-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 :).
Moderators
January 31, 2022
Offline
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.
1 Guest(s)
