4 Steps to Rocking V-Lookup!

The beauty of having your own blog and not being tied down to a niche, is being able to talk about anything and everything, including Excel v-lookup. V-lookup was a great mystery to me for years. One of my bosses tried to teach me but I couldn’t grasp the concept. I took classes but without an instructor available for questions, I failed miserably. This went on for years until one day, I just got it.

What is v-lookup?

For anyone working with large amounts of data, V-Lookup is the difference of a task taking an hour or taking 4 days. V-lookup is an action called a formula. Let’s say you have spreadsheet A that you need to enter data in but there is another tab or spreadsheet that has all the info you need. You can magically bring over all the info into spreadsheet A, in lieu of manually inputting every single cell.

The best thing about V-Lookup is once you master it, you are a rock star since many people still don’t grasp it. Below is a step by step guide to performing a v-lookup. At the end of this post, I’ve included a downloadable spreadsheet that I created for practice. It might be best to read this post on a tablet while working on the download on a laptop/PC.

Step 1

Open the spreadsheet. You have 2 tabs: Final Sales (1) and Master Sales (2). Ensure you are on the Final Sales tab.

Your goal? You need info for tab 1. All of this info can be found on tab 2 and you are going to bring over only what you need.

1. Click in the first cell you wish to fill out.
2. Click on the Fx for formula.
3. Ensure that V-Lookup is highlighted.
4. Click ok.

Step 2

Now this pops up. Don’t be scared, we are going to fill it out.

Tip: What makes this whole function work is there has to be a column of information on tab 1 that is also on tab 2. Excel uses this information to make a match and bring over the info. We are going to use the info in the column titled ID #. I call it the unique identifier.

1. Click on cell A2. Clicking on this cell immediately populates A2 in the first field (lookup_value). Next, hit F4 3 times till the dollar sign is in front of the A. More about that later.

2. Next, place your cursor in the 2nd field (Table_array), click on tab 2 (master sales), then click where I’ve circled in red. This highlights the entire spreadsheet. I have an arrow drawn to show you what it produces. Now, hit F4 just once.

3. Place your cursor in the next field (Col_index_num). You’ll notice excel then takes you back to the first tab (final sales). Place a 5 there. Basically, we are telling Excel to lookup 95823 on that second tab and bring over the information that resides in the 5th column that is in the same row as 95823. 

4. In the next field (Range_lookup), enter false, click ok. Congratulations! The worst is over!

Step 3

Left click and hold on the bottom right corner cell C2 while dragging your cursor down over cells C3 through C9, release. Column C is filled out. Here is the before and after screen shots.

Step 4

You now have 2 options to fill out columns D and E.

Option 1

Repeat steps 1-3, except replace the 5 with a 6 for column D and a 7 for column E:

Option 2

1. Click again on C2 in the bottom right hand corner (just like you did in Step 3) but instead of dragging down, drag to the right till you cover E2, release. You should have the word East in cells D2 and E2.
2. Click on cell D2 and in the formula bar, delete the 5 and replace it with a 6 (I’ve circled in red where you do this). Again, this is to tell excel how many columns to count over to retrieve the info from the master sales tab. Hit enter.
3. Click on cell E2, and in the formula bar, delete the 5 and replace it with a 7. Hit enter.
4. Click on both cells D2 and E2 by left clicking and dragging from D2 to E2. Release. Then left click on the bottom right hand corner of E2, hold it and drag down over rows 3 to 9. Release.

Troubleshooting/Helpful Hints

First let me say I’m not an excel genius. I don’t know if the following is necessary but may solve issues if you find the right info isn’t coming over.

1. Make sure the unique identifier (this was Column A) is in the first column of both tabs being used.
2. I make sure there aren’t blank rows between the data.
3. If info isn’t coming over properly, it might be because the lookup value (that first column) is a number but stored as a text. To fix this, highlight the first column and where I have circled in red, change it to a number. Same steps for tab 2. Basically you want the unique identifiers (ID #) to be the same format. 
4. Remember when I told you to hit F4 three times for the lookup value then just once for the table array? I like to think of F4 as a locking mechanism. When you hit F4 so the lookup value reads $A2 instead of just A2, this is preparing the cell for the future action of clicking and dragging down. If you didn’t hit F4 three times, the information retrieved for cells C3 through C9, would be referencing A2 for all of them. Hitting F4 three times, tells excel that as you click and drag down to each cell, reference the cell in column A that is in the same row as the cell in column C. When you hit F4 just once for the table array to get $1:$1048576 in lieu of just 1:1048576, this told Excel to reference every cell of the master sales tab. 
5. I like to remove any errors on any cells. I don’t know if this is right, but I do it. It’s that little green notch in the top right hand corner of each cell. I simply right-click and ignore error.

I hope this cracks the V-lookup mystery for you! Any questions, let me know! Also, here is that download for practice!

hotmessexceltutorial