12

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?

Endle_Zhenbo
  • 538
  • 4
  • 23
James
  • 209
  • 3
  • 13
  • I don't have any experience with this plugin, but you could give it a try: https://www.microsoft.com/en-us/download/details.aspx?id=15011. – user2027202827 Apr 29 '17 at 05:51
  • 1
    Your second formula works for me - put in without {} but with Ctrl-Shift-Enter – Tom Sharpe Apr 29 '17 at 09:56
  • 1
    Your array formula is fine - did you enter it with Control, Shift and Enter ? I followed this page - http://www.excelforum.com/excel-formulas-and-functions/665518-partial-match-in-an-array.html - to come up with an answer but discovered it was the same as your `{Index($F$2:$F$4,match(TRUE,isnumber(find($E$2:$E$4,A2)),0))}`. Make sure you enter it correctly: enter formula with no `{}`s, then F2 to edit formula, then Control+Shift+Enter to make it an array formula. Works for me no issues. – Robin Mackenzie May 02 '17 at 11:29
  • The problem I was getting was that it would MATCH a substring if that substring was the first value in the FIND array. In other words, expanded: find{Spiderman, Titanic, Dark Knight}.. formula would work only if cell had "Spiderman" in it. The real issue was that I am on mac and used CMD+Shift+Enter rather than CTRL+SHIFT+ENTER. Formula works with CTRL+SHIFT+ENTER. Thanks for the help. – James May 02 '17 at 16:14

2 Answers2

8

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)

Gowtham Shiva
  • 3,802
  • 2
  • 11
  • 27
  • Hi,thanks for the answer here. Your formula =MATCH(""&E2&"",A:A,0) is great but what if there are multiple matches for E2 in column A? The match formula is returning the row of the movie title match, which could appear dozens of times in the list. Imagine if 2,000 video titles were in column A, and you were match them with 50 Movie titles and descriptions in Columns E and F (see edits in description) – James Apr 30 '17 at 00:14
  • @James if there are multiple occurrences of movie titles in column A, wont the descriptions be the same for all titles. If thats the case the formula would work perfectly. If you do not think so, please update the question with an example – Gowtham Shiva Apr 30 '17 at 06:46
  • I think better understanding your formula in column B would help. There are 2,000 video titles and 50 movie titles to search for. What would the formula be in B51 (next to "Story behind Get Out Movie")? Since the helper column is helping column E, the formula will not work after those 50 titles. I may be misunderstanding the process but understanding B51's formula would help – James Apr 30 '17 at 16:16
  • 1
    Idea 1 is breaking down for me when the list in column A is longer than column F – Robin Mackenzie May 02 '17 at 11:36
0

On my opinion, this can't be solved by formula alone.
Using VBA might do.

First, add a new module.

Second, add the function below: 

Function GetRow(xCell As Range, xRange As Range)
    i = 2
    Do
        If Cells(i, xRange.Column).Value = "" Then
            Exit Do
        ElseIf InStr(1, Cells(xCell.Row, xCell.Column).Value, Cells(i, xRange.Column).Value) > 0 Then
            GetRow = Cells(i, xRange.Column + 1).Value
        End If
        i = i + 1
    Loop
End Function

Third, add the formula in all rows in column B:

=GetRow($A7; E:E)
Paul Co
  • 447
  • 2
  • 9