CODEWITHSUNDEEP
excelexcel-formulaexcel-2010spreadsheetstring-matching
James
James

Reputation: 209

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?

Upvotes: 12

Views: 4764

Answers (2)

PQ Co
PQ Co

Reputation: 507

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)

Upvotes: 0

Gowtham Shiva
Gowtham Shiva

Reputation: 3875

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)

Upvotes: 8

Related Questions