 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 four 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 zero option which means you want an exact result. Then type a closed parentheses and press enter and you get a number five which is Lola's position in the array of information you selected and anchored. You can follow this 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 zero for an exact result. Type a closed parentheses, press enter and you get a number two 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 closed parentheses, press enter and you get the answer. 100 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 XL needs to find the information followed by a comma. Now type the word match and an open parentheses. Select g6 which indicates XL needs to find a student name and then type a comma. Now select the area where XL will find the names of the students and anchor with F4. Then type a comma and write a zero for an exact result. Now type a closed 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 XL where to look and anchor with F4. Type a comma and then select the zero option to get an exact result. Now type a closed parentheses followed by a comma. Then type the number one which indicates where the name will be found in column one on the table. Now type a closed 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 lookup functions in XL 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.