Copying values in excel through a macro

Question

New to macros, and I am looking for a little insight.

I am looking to have a cell display information from a table on a separate sheet based on the value in a box that is selected.

I have 5 cells that return a title to a movie based on it's rating which is pulled from a table called Movies. I also have 5 cells next to it that return the year and then 5 cells next to that with the rating. Here is what it looks like:

                      A                        B      C
   ______________________________________________________
1 | Eternal Sunshine of the Spotless Mind  |  2004 | 8.5 |
2 | 3 Idiots                               |  2009 | 8.2 |
3 | Before Sunrise                         |  1995 | 8.1 |
4 | Groundhog Day                          |  1993 | 8.1 |
5 | (500) Days of Summer                   |  2009 | 8.0 |

I want to make it so that when cell A1 is selected, the plot is pulled from the Movies table in the movies worksheet.

Worksheet = movies
Table = Movies
Column = Plot

I need to be able to match both the title and the year as there are multiple movies with the same name in my Movies table.

Here is the macro I have created so far to test with (Result1 is the name for A1, Result2 is the name for A2, etc.):

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Not Intersect(Target, Range("Result1")) Is Nothing Then
        MsgBox Target.Address & " is Result1."
    ElseIf Not Intersect(Target, Range("Result2")) Is Nothing Then
        MsgBox Target.Address & " is Result2."
    ElseIf Not Intersect(Target, Range("Result3")) Is Nothing Then
        MsgBox Target.Address & " is Result3."
    ElseIf Not Intersect(Target, Range("Result4")) Is Nothing Then
        MsgBox Target.Address & " is Result4."
    ElseIf Not Intersect(Target, Range("Result5")) Is Nothing Then
        MsgBox Target.Address & " is Result5."
    Else
    End If
End Sub

I would like to copy the plot of the movie in the selected cell into cell B7.

For example, if Result1 was selected, it will find Eternal Sunshine of the Spotless Mind in the movie table and output the plot of it in B7.

Thanks for any help!

EDIT: This is what the Movies table looks like:

    ID   Title                            Year   Duration   Rating   Plot
   _____________________________________________________________________________________________________________________________________________________________________________________
  |  1 | (500) Days of Summer           | 2009 |  95 min  |   8.0  | An offbeat romantic comedy about a woman who doesnt believe true love exists, and the young man who falls for her. |

Answer 1

I'd probably use AutoFilter. Based on the structure of your movies table that you're referencing (which I don't have at my disposal), you would need to modify the AutoFilter Field values, and make sure you define tblRange.

Revised per OP comments & example file structure

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim movieTitle As String
Dim movieYear As String

If Not Intersect(Target, Range("Result1")) Is Nothing Then
    movieTitle = Range("Result1").Value 'Modified because you use merged cells...
    movieYear = Range("Result1").Offset(0, 1).Value 'modified.
    GetMovieInfo movieTitle, movieYear
End If
If Not Intersect(Target, Range("Result2")) Is Nothing Then
    movieTitle = Range("Result2").Value 'Modified because you use merged cells...
    movieYear = Range("Result2").Offset(0, 1).Value 'modified.
    GetMovieInfo movieTitle, movieYear
End If
If Not Intersect(Target, Range("Result3")) Is Nothing Then
    movieTitle = Range("Result3").Value 'Modified because you use merged cells...
    movieYear = Range("Result3").Offset(0, 1).Value 'modified.
    GetMovieInfo movieTitle, movieYear
End If
If Not Intersect(Target, Range("Result4")) Is Nothing Then
    movieTitle = Range("Result4").Value 'Modified because you use merged cells...
    movieYear = Range("Result4").Offset(0, 1).Value 'modified.
    GetMovieInfo movieTitle, movieYear
End If
If Not Intersect(Target, Range("Result5")) Is Nothing Then
    movieTitle = Range("Result5").Value 'Modified because you use merged cells...
    movieYear = Range("Result5").Offset(0, 1).Value 'modified.
    GetMovieInfo movieTitle, movieYear
End If

End Sub

The subroutine, GetMovieInfo will filter the Movies table and return the results from the 6th column (plot) in a message box for you.

Sub GetMovieInfo(movieTitle As String, movieYear As String)
Dim tblRange As Range
Set tblRange = Sheets("movies").Range("Movies")
With tblRange
 .AutoFilter Field:=2, Criteria1:=movieTitle  '<change to filter column "2"
 .AutoFilter Field:=3, Criteria1:=movieYear  'change to filter to column "3"
    With .SpecialCells(xlCellTypeVisible)
        If .Areas.Count > 1 Then
            MsgBox .Areas(2).Cells(1, 10).Value
        Else:
            MsgBox .Areas(1).Cells(1, 10).Value
        End If
    End With
 .AutoFilter
End With
End Sub

Answer 2

I propose to use Find Range property. The function could be as follow:

Function GiveMeMoviePlot(MovieRange As Range, MovieTitle As String, _
            MovieYear As String)
    'pass movieTable to MovieRange

Dim A As Range
Dim checkAddress As String

Set A = MovieRange.Find(MovieTitle, , xlValues, xlWhole, , xlNext, False)
checkAddress = A.Address

If Not A Is Nothing Then

    Do
        Debug.Print A.Address
        If A.Offset(0, 1) = MovieYear Then
            'found
            GiveMeMoviePlot = A.Offset(0, 4)
            Exit Function
        Else
            Set A = MovieRange.FindNext(A)

        End If

    Loop While A.Address <> checkAddress

End If

        GiveMeMoviePlot = "Nothing found"
End Function

The rest of the logic is quite similar to that of @DavidZemens