What you Can Do About It – Learning the Vlookup Formula by Example

I can’t even count on one hand how many times in a day I resort to using the Vlookup formula for solving questions and problems at work.  When you think about it, it’s really the best thing ever.  Being able to merge multiple sets of information into one to make a set of super information.  In my eyes the Vlookup is a tool to merge information.  I want to show you a practical real life example on how you can use the formula to get the solutions you need.

Using the Vlookup Formula to Find the Months On Hand for Each Item Number – A Practical Example

As the Purchasing/Planning and Inventory Supervisor I need to understand what’s going on in the plant with our inventory at all times.  It’s important to optimize inventory levels and quantities to match our customer demand levels.  Too little, and we could get stuck not being able to fulfill a customer order on time.  Too much inventory and that’s just not good for anyone.  It ties up cash flow, it consumes valuable floor space, and it also consumes labor and time (this is all waste). It’s important that I always know where and what slow moving inventory I have and also what inventory I need to start restocking more of.  This allows me to understand what items I need to come up with creative solutions to reduce inventory for and it also allows me to get system tools in place to manage my faster moving items.   In order to do this, I need to pull an on hand inventory report and I also need to pull a usage report.  I typically pull the last 12 months of usage for each part (sometimes I look specifically at the last 3 or 6 months as well to see what the average usage rates are).  Once I have both sets of data, I can now use the Vlookup formula to pull in the last 12 months usage into my on hand inventory data set.  Let’s have a look at how to do this.

STEP 1: Gather Your Data and Setup Two Worksheets in Your Workbook.

Getting the data required is the first step of the process.  Pull the data from your software for each table that is required. Once you’ve got your data, you can put each set of data on their own worksheet within your workbook.  This will allow you to keep things clean.  For this example, on one sheet I’ll place my on hand inventory and on the other I’ll place my inventory usage data.  Now that we’ve got the data organized, we can use the Vlookup formula to pull over the usage data into our inventory table for each item.

STEP 2: Use the Vlookup Formula to Merge Your Data.

Once you understand how the Vlookup formula works, there isn’t much to it.  In the screenshot above I’m looking up the item number in column B (cell B3). The second step to this is to then highlight the column range from the column that you will be matching against (item number – Column B – on the usage table) to the column that you want to return your result from (L12 Usage Qty – Column D – on the usage table).  The third step is to select the column number that you want your value to return from on your usage table.  In this case we want the 3rd column of the highlighted area (i.e. L12 usage Qty). Finally the last step of the Vlookup formula is to tell it whether you want to find the exact match (false) or an approximate match (true).  In this case of course we want to return the exact match, which makes our value false.  As you can see above, column E of our On Hand Inventory Table now tells you the L12 months usage for each item (note: you can either copy and paste the formula in the cells or use the little square in the bottom right hand corner of the cell to fill the rest of your data set).

STEP 3: Calculating Months On Hand Using Other Formulas

Now that you’ve merged the usage data into the on hand inventory table we can calculate the months on hand.  This is simply the on hand quantity/(L12 Usage Qty/12).  This will then tell us exactly how many months on hand we have for any of the items that we have inventory for.  You can then use filtering and conditional formatting to identify items that you’d like to create action items for based on a certain criteria/# of months.  As you can see being able to merge your usage data with your on hand inventory can be extremely beneficial to a business/employer.

Although the Vlookup formula can seem pretty intimidating, you can see why it is appealing to employers that the employees that they hire understand how to use this awesome formula. As you start to understand the formula you’ll realize that you can pretty much merge any type of information as long as you have a lookup key between the tables that you’re merging your information from.  There are other technicalities that you can learn about the Vlookup formula but as you become seasoned with the formula you’ll be able to pick those things up quickly.  To help you with that process, I’ve included several free / paid resources that you can use to become the employee that each business needs and wants to hire.

FREE RESOURCES TO TAKE YOUR VLOOKUP GAME TO THE NEXT LEVEL