INSTRUCTOR: Today, we'll show how to use MATCH and INDEX, two of the most powerful lookup functions in Excel 2016. We showed you how to use VLOOKUP in video number 4 in our Excel series. But as you will see, MATCH and INDEX functions are more dynamic and can do things VLOOKUP cannot. 

Rod, here. And welcome to Burn to Learn. Let's begin with MATCH. This function searches an array of data and identifies the position of the result you want. Here's a library book control spreadsheet that has three variables-- names, months, and book names. We want to identify the cell position of Lola Jacobson. And here's how you do it. 

Write an equal sign and the word MATCH with an open parentheses. Select H5, which means Lola Jacobson's name. Now type a comma. Then select the names in column B. 

Now, anchor the information by pressing F4 on your keyboard. Anchoring makes sure Excel will stay locked in on this information. Now, select the 0 option, which means you want an exact result. Then type a close parentheses and press Enter. And you get a number 5, which is Lola's position in the array of information you selected and anchored. 

You can follow the same process if you want to know the position of the month February. Write an equal sign, the word MATCH and an open parentheses followed by a comma. Select I5 plus a comma and select the horizontal area where the months are. Don't forget to press F4 to anchor the information. And select option 0 for an exact result. Type of close parentheses, press Enter, and you get a number 2, which is the position of February. 

Now, let's look at how to use the INDEX function. The INDEX function looks up an array of data and gives you a value or the description of a value from within a table. Let's see how it works in our table. 

You want to know which book Lola Jacobson asked for in February. Here's what you do. Type an equal sign and the word INDEX followed by an open parentheses. Now, select the data where the book names are. Anchor what you selected by pressing F4 and then type a comma. 

Now, select H6, which is Lola Jacobson's position, and then type a comma. Then, select I6, which is the position of February in the spreadsheet. Now, type a close parentheses. Press Enter. And you get the answer, Hundred Years of Solitude. 

Please like this video and leave a comment after watching. 

Now, we'll show the real power of this by combining both INDEX and MATCH to find a specific piece of data. Type an equal sign, the word INDEX, and an open parentheses. Now, select the whole table, but without headings where Excel needs to find the information followed by a comma. 

Now, type the word MATCH and an open parentheses. Select G6, which indicates Excel needs to find a student name and then type a comma. Now, select the area where Excel will find the names of the students and anchor with F4. Then type a comma and write a 0 for an exact result. 

Now, type a close parentheses followed by a comma. Now, type the word MATCH, followed by an open parentheses. And then select H6 for the month, followed by a comma. 

Now, horizontally select B5 through E5, which tells Excel where to look and anchor with F4. Type a comma and then select the 0 option to get an exact result. Now, type a close parentheses, followed by a comma. 

Then type the number 1, which indicates where the name will be found in column 1 on the table. Now, type a close parentheses and press Enter. And the answers you get are Melissa Kerr selected the book Life of Pi in March. 

If you change the formula values, new answers appear automatically. Now, you know how to use MATCH and INDEX, the two most powerful look up functions in Excel 2016. You can practice using these functions by clicking on the Exercise button below. As the saying goes, practice makes perfect. 

If you liked this video, please comment, share it, and hit the Like button. And please remember to subscribe to our channel and receive future videos automatically. Thanks so much for watching. 

[MUSIC PLAYING]