- Think all formulas is an Excel 2016 look complicated? You really need to watch this video. Rod here. And today we will show you how to use the VLOOKUP formula, which is very useful when you want to quickly analyze a large database to get specific answers. 

Imagine a situation where you need answers about the current status of a stock inventory before requisitioning more stock. Here's a piece of a large stock inventory in TABLE 1. There are three columns in this table, PART NUMBER, SUPPLIER, and QUANTITY. Using VLOOKUP formula we will quickly analyze TABLE 1 and put the answers we want on the number of parts in stock in TABLE 2. 

First, select the cell where you want the answer to be. Put in an equal sign in the cell and then type the word "VLOOKUP" after the equal sign. Next, type and open parentheses. This will open the formula area. 

Now choose the cell that needs to be found. Now place a comma in the formula. The comma is like a break. Now let's select the range of data where the answer is to be found. And now press F4, which anchors the range and ensures the range stays the same. And place a comma in the formula. 

Put the column number where the answer is to be found. In this case, it's column number three. We want to know the quantity. Now place another comma. And then finally, place a 0 or the word "FALSE" in the formula to indicate an exact amount is required. 

Close the formula with a closed parentheses and press Enter. Note that the answer is two. If we look at TABLE 1 we will scroll down and see on row 26 the exact model number we wanted to know about. And there are two in stock. 

Now if you want answers in the rest of the lines for your stock models, place your pointer in the bottom right corner of the cell you're working with and then drag down. Excel automatically gives you the answers. There is one more thing to note. You'll see that on row 8 there is a similar model number but it's not identical to the row in row 26. VLOOKUP will only type the one, or report on the one, that is exactly what is typed in the formula. 

Now let's show you the process again but this time, put new information on SUPPLIERS in TABLE 3. First select the cell where you want this new information to appear. Then put an equal sign in the cell and type in the word "VLOOKUP" after the equal sign. 

Next type in an open parentheses, which opens the formulas. Now choose a cell that needs to be found. Note the cell becomes part of the formula. Place a comma in the formula. Remember, a comma is a break. 

Select a range of data where the answers to be found. Press F4, which anchors the range and ensures the range stays the same. Now place a comma in the formula and put the column number where the answer is to be found. In this case, it's column number two. 

Place another comma and then place a 0 or the word "FALSE" in the formulas to indicate an exact amount is required. And close the formula with a closed parentheses, and press Enter. As you can see, for model number 9LK the supplier is S8. And therefore, that's the answer. 

Now you don't have to repeat this process to get answer in the remaining models. You just put your pointer in the bottom right corner and drag down. Excel gives you the answers automatically. Also remember that if the same item appears more than once in your inventory, VLOOKUP will only report on the first item. 

Now you know how quick and easy it is to use VLOOKUP in Excel. We want to know if you found this video helpful. Comment below and let us know. If you want personal coaching click on our one-on-one coaching link. 

Also, please subscribe to Burn to Learn. Subscribing is free and helps us a lot. And help us continue to offer our videos for free by clicking our patron link. And be sure to watch our next video where we help you learn to use another Excel formula.