As an ecommerce entrepreneur, there is no escaping spreadsheets.
Whether setting up a product or inventory feed for your sales channels, analyzing sales data, reconciling receipts in Quickbooks, or working on a quick budget for your next purchase, you’re going to end up working in a spreadsheet. Being comfortable working in Excel (or any other spreadsheet software) has T-R-E-M-E-N-D-O-U-S advantages.
One could probably build an entire business teaching Excel.
In order to manage the spreadsheet chaos that comes with selling online, it’s imperative that you learn Vlookup. I find myself using it many, many times a day, and it has saved me thousands of hours and dollars over the course of my career.
So what is the Vlookup function? Here’s a quick scenario:
I have 2 spreadsheets from my distributor. One is a product data file. It has a SKU, title, some basic feature and spec columns, and a price. It looks like this:
However, it doesn’t have their available inventory. That comes on a completely separate list. Like this one:
Of course, I really need to get these two lists together into one file. And I DO NOT want to spend hours scrolling around the page looking for the SKU I need and manually typing in the qty available.
So I use the Vlookup function!
Vlookup will find the data I want in one sheet based on the data in another. As long as there is a common column in both files (like my SKU), you can use Vlookup. Here’s how to do it.
Vlookup in 4 steps
1) Start your formula
Create your new destination column (Qty Available). To start the formula, type the following into the field where you want the value to go:
=vlookup(
Then, select the cell (in my case, the SKU) in that row that you want to search for in your other sheet. Afterwards, add a comma.
2) Tell Vlookup where to search
Now that you’ve told Vlookup that you want to search based on that SKU, you need to go to your other file and tell it where to search.
Go to the other sheet, and select the group of columns in which you wish to search.
You’ll want to start with your SKU column, click the column and hold, drag over to the column where your target value (in our case, the qty available) sits, and release. Add another comma.
You’ve now selected a group of columns that Vlookup will use to execute your search. In our case, columns A and B.
3) Tell Vlookup how many columns between your values
Now we’ve selected our target area, we need to tell Vlookup where the value is. In our case, it’s simple. There are only 2 columns in our file, and the target is in the second one, so the value we’d put here is 2. (don’t forget the comma!)
However, Vlookup can be used on a large group of columns, so in the event there were 10 columns total on our file, and our SKU was in the first, and the qty available in the 5th, we would use the value of 5. Get it?
Here’ s a SUPER helpful shortcut for Excel users. When you begin dragging your columns, Excel will actually count the columns for you in the corner of your spreadsheet. (see above screenshot)
It will be in the top or bottom corner of your spreadsheet, and as long as you stop your drag on the target column, you can just enter the value that Excel counts for you.
4) Enter a 0
This is an easy one. End your function with a 0. Why? A 0 value tells Vlookup that you want to find the value that matches EXACTLY to the column we’re using to search. In other words, I don’t want to return a value for a similar SKU, I want the value for my exact SKU. At this point, there are no more arguments to add to this formula, so you don’t need any more commas. You can simply hit Enter.
And that’s it! When we’re done, our formula looks like this:
You can see all 4 parts: Our starting cell, our target search area, the number of columns over our target value sits, and a 0!
All you need to do now is copy this cell, and paste it in the other cells in your file. They will execute the same function based on the SKU in their row, and you’ll pull in your qty available from the other file.
Final thoughts
While Vlookup is only one function in the world of spreadsheet management, its perhaps the most valuable and impactful one you can learn. By the way, you can also use its sister function, Hlookup, to search for values in Horizontal rows instead of Vertical columns.
Take 5 minutes and learn Vlookup. I guarantee that once you do, you’ll feel more equipped to tackle your spreadsheets.
