Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel - Match substring from list of choices - INDEX, MATCH, and FIND used together

I'd like to search for a specific movie title within a list of video titles, search for MATCH, and use Index to return its description. I know this can be done with a text search in a filter via Column A, but I'd like to do it with a formula.

**EDIT: I think the first version of this question made it seem like I have only a few movies and titles. These stats may help:

Column A: 2,000 Total Video Titles (movie titles can appear more than once)
Column E: 50 Movie Titles
Column F: 50 Movie Descriptions

Example:

Video titles (2000)             Movie Titles    Movie Description
Spiderman Review and BTS        Spiderman   Spiderman_description
Dark Knight clips               Star Wars   Star Wars_description
Fun Fact Star Wars              Dark Knight Dark Night_description
Why I love Dark Knight                  
Dark Knight highlight                   
Always watch dark knight alone      

Within B2, I can type

=if(isnumber(find("Spiderman",A2)),index(F2:F4,match("Spiderman",E2:E4,0)))

I can then repeat this formula for each movie, but the full list is over 50 movies so far. I'd like to create something like this:

{Index($F$2:$F$4,match(TRUE,isnumber(find($E$2:$E$4,A2)),0))}

This way, I'd search A2 to see if FIND returns ANY match from the list, then return the description using INDEX. But this formula is not working. Where did I go wrong?

like image 297
James Avatar asked Apr 29 '17 05:04

James


1 Answers

Two ideas,

1) Have a helper column in column B and get the description in column C. enter image description here

Formula in column B,

=MATCH("*"&E2&"*",A:A,0)

Formula in column C,

=INDEX(E:F,MATCH(ROW(),B:B,0),2)

2) Doing it the other way around with a simple index match formula, enter image description here

Formula in column G (original title),

=INDEX(A:A,MATCH("*"&E2&"*",A:A,0),1)

like image 64
Gowtham Shiva Avatar answered Nov 06 '22 10:11

Gowtham Shiva