Identify Data Patterns with a VLOOKUP Formula

In this video, you will analyze the actor, director, and genre data for your specific movie idea.

To do this, you will look up the specific data you need using a V-lookup formula.

You could manually look up each data point.

You might: sort the spreadsheet, find a data point, and copy it into the spreadsheet.

But that's a LOT of work especially with a ton of data.

And you would have to redo it every time a new movie comes out.

Fortunately, formulas make it easy to lookup values in a spreadsheet.

A LOOKUP formula lets you search a spreadsheet for a specific word, like "Comedy" and returns a value from another row or column -- in this case, the average ROI.

In this video, you will use a VLOOKUP formula to find a keyword across columns, or verticals.

This example looks for keywords in the genre sheet.

Use the same formula to look for directors and actors.

In your movie data spreadsheet, create a new column for "Average ROI." Click the cell beside the first genre.

Begin typing "= VLOOKUP" and select "VLOOKUP." Use the help text to write the formula.

First enter the "Search Key." This is the word you want to match in the genre sheet.

In this example, that's "Horror." Select the cell.

Then type a comma.

Next, enter the "Range." This is the area where the computer looks for the word "Horror." Click to the genre sheet.

The formula from your movie sheet follows you.

Select all the data, then type a comma.

Next, enter the index.

The index tells the computer how far the Search Key is from the data point.

The return on investment is three columns from the genre.

Type another comma.

Finally, the formula assumes that the spreadsheet is Sorted.

In this case, the spreadsheet is not sorted.

Type "FALSE." Make sure your formula is complete.

End it with a closed parenthesis.

Press Enter.

Great! Now the computer gets the average return on investment for the horror genre.

Check the value in the genre spreadsheet.

They match! Using a formula is faster and more accurate than finding the values yourself.

Plus, the values update automatically as data in the spreadsheet changes.

Copy the formula for other genres, directors, and actors by dragging the formula down.

The cells with the copied formulas return errors.

Check the formula beside "Director." It looks for the search key, or the director's name.

But the formula looks in the genres spreadsheet.

That won't work! Change the range so the computer looks for the search key in the directors spreadsheet.

Then, type the index and sort status again.

Great! Now the formula: looks for the director's name; in the directors spreadsheet, and returns the value that is three columns away from an unsorted spreadsheet.

Press Enter.

Awesome! Now the sheet returns the correct ROI for this director.

Change the range in the actors rows, too, so the formula uses the appropriate range in each row.

If you still get error messages, don't worry.

Not all directors or actors will be in the movie data spreadsheet, especially if they have not produced a major film recently.

To check, return to the correct tab in your spreadsheet and sort the sheet.

This actor is missing.

That explains the error message.

Even if you do not have all the data points for your movie, you still have plenty of data to decide which movie to greenlight.

Data can help you make decisions, but it can not make the decision for you! Finally, open your group's movie ideas document.

Re-read the inferences about what makes a successful movie.

Add the word "Data" underneath initial thoughts.

Was there anything surprising to you?

Maybe you inferred that comedies were very profitable, but their returns on investment are not as good as you expected.

Perhaps you thought the movie with your favorite celebrity would be a huge success -- only to find that their films are not as successful as you thought.

Record your analysis beside "Data." You will use these points when you discuss your observations in the next video.

Now, it's your turn: Use a VLOOKUP formula to automatically get the calculated average return on investment for each genre, director, and cast members for your movie.

Enter the search key, range, index, and sort status and press Enter.

Check the average return on investment in the movie data spreadsheet.

Copy the formula to the other genres, directors, and actors.

Change the range in the formulas so that the program draws from the right data.

Check the formulas to make sure they match the calculated ROIs in the genre, director, and actor tabs.

When you have finished, check in with your group members to see if they need help using the VLOOKUP formula for their movies.

Then, add your thoughts and reflections to your group's movie ideas document.

When you are all ready, move on to the next video, where you will discuss your findings with your group.