Excel: Search multiple columns for multiple words
Timothy Lukas H.
I have a big list of movies, each one has 1-8 keywords in 8 diffrent columns (Key1-Keyw8).
I now need to sort through all movies for movies which have let's say 3 keywords.
Example: I want to find all movies with the keywords: Water, Ship, Clouds.
Now every film that has all those 3 keywords should be displayed. The keywords can be in any column.
I found out that I can do filters like this with advanced filter, but I now have a problem with how they work.
If I put a word in the first column, it will only search if the Keyw1 contains the word: 
I then thought I could simply make multiple searches: 
This kinda works, it gives me some results. But it also leaves some rows out: 
But some fell through: 
Row 2 contains the keyword, but did not get displayed.
Even if this would work, if I want to search for 8 keywords, I would need to duplicate the word 40'000 times to find all results.
What am I doing wrong? Or how else could I do it?
Ron Rosenfeld
If you are going to look for so many keywords, you need a different strategy. You can modify below for any number of keywords to be found at the same time
I would set up a Helper column, with a formula that evaluates to TRUE or FALSE, and then filter on that.
For example:
- KeyWords is a
Named Rangereferring to$G$2:$G$3
- In the table below:
E2 (FilterCol): =SUMPRODUCT(--ISNUMBER(MATCH(KeyWords,A2:D2,0)))=COUNTA(KeyWords)
- Fill Down and then filter on
TRUE
You can certainly Hide the FilterCol, or put it somewhere distant on the worksheet where it won't be seen
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
- Prev: 403 forbidden access to non-Wordpress subfolder
- Next: Modularity in Excel - calculation "blocks"
Related
Related Related
- 1
SQL query that search in multiple columns multiple words
- 2
Search: Match all words from multiple columns
- 3
Search: Match all words from multiple columns
- 4
Search Multiple Words PHP
- 5
Solr multiple words search
- 6
chosen search for multiple words
- 7
Searching an excel with two search words (multiple lookup) using vba macro
- 8
Search multiple columns for multiple values
- 9
How to search by multiple columns?
- 10
How to search in multiple columns?
- 11
Search across multiple columns
- 12
MySQL search in multiple columns
- 13
MYSQL - Search words in multiple coumns
- 14
jQuery selector, search multiple words
- 15
Search function looking for multiple words
- 16
jQuery selector, search multiple words
- 17
php MySQL multiple words search
- 18
Excel - SUMIFS for multiple columns
- 19
Excel COUNTIFS Multiple Columns
- 20
Matching Multiple Columns Excel
- 21
Excel Comparing Multiple Columns
- 22
Excel, sort multiple columns
- 23
Excel columns to multiple rows
- 24
Vlookup multiple columns in excel
- 25
Filter multiple columns in Excel
- 26
Excel Rank Multiple Columns
- 27
Excel with lookup in multiple columns
- 28
Excel IF multiple columns
- 29
Excel IF Statement for finding multiple words
Comments