Below is a 4-minute screencast that shows how to use VLOOKUP. This video supplements our How to Use VLOOKUP in Excel tutorial. That article includes more details and a downloadable Excel practice file.
In this video, I’ll show how to use Excel’s VLOOKUP function. I’ll be using Excel and Office 365, but you can use earlier versions. I’ll be demonstrating an example where we need to look up the political party that matches a Pcode. I have downloaded the practice file, and will show how to look up values from a different worksheet.
You can find the practice worksheet at timeatlas.com/vlookup-tutorial. There will be a download button toward the bottom of the page.
You’ll notice the workbook has three tabs. I’ll start with Example 2- Voter Sheet. If you look at Column C, there are codes that represent a political party. The problem is no one knows what they mean. We need to translate them to something meaningful.
To start, I’m going to label Column D to Political Party. Next, I need to look up the Pcode of “A” and see what party it belongs to in populate cell D2. If I open the Party Code Sheet, I see the “A” translates to the “Democratic party”. However, I don’t want to toggle between sheets for every entry.
Instead, I’ll use VLOOKUP. Click in cell D2. From the ribbon, click Formulas. From the far left, click Insert function. The Insert Function dialogue opens. Excel will probably show your most recently used functions.
From the category dropdown menu, select Lookup and Reference. Scroll to the bottom and choose VLOOKUP. Notice how Excel defines the function. Click OK.
You now have a blank Function Arguments dialogue. In the Lookup value box, type C2. Notice how Excel has put an A to the right of the equal sign. Next, we’ll define our table array. This is where we want Excel to look.
Click in the table array field. Now click the Party Code Sheet and click cell A1. Press Ctrl + Shift, and then your right arrow and down arrow keys. This should highlight the whole range. Notice how Excel added the sheet name.
Since we don’t want our formula to shift as move down the worksheet, I’ll add $ signs around the range. I can either enter the $ signs manually, or cycle through using my F4 key. But now we have to tell Excel which column to search for our value. Click in the column index num field and type 2.
In the range lookup field, type false. This tells Excel we want an exact match. Excel will show the lookup value of Democratic in two places. Click OK to accept the formula. You should see Democratic in cell D2 on the Voter Sheet. Click cell D2.
Move your mouse to the bottom right corner. Double click when you see the big cross change to a tiny cross. This copies our formula down the column.
If you wish to see an example of using an approximate match on the same worksheet, check out example 1 on the practice file.
Thanks for watching. You can see the full tutorial with additional details at timeatlas.com/vlookup-tutorial.