Excel Video 61 is the first video in a new series covering VLOOKUP. If you’ve never seen VLOOKUP before, you’ll find it to be a real time-saver when it comes to managing multiple sets of data. If you’re familiar with VLOOKUP, stay tuned. Once we’re through the introduction I’ll add some tips and tricks you might not be aware of.
VLOOKUP is designed to look up information in a table. The VLOOKUP function has four parts. The syntax goes like this. =VLOOKUP(cell or information you want to look up, table containing the cell or information, the column in the table with the information you want, and an optional true or false value). Each part of a VLOOKUP formula is separated by a comma. Don’t worry about the optional value yet, set it to false and we’ll cover that in future videos.
Step 1: I almost always put a cell reference in the first field since I usually want to look up multiple cells in the same table and want to copy the VLOOKUP formula when I’m done.
Step 2: When I enter the table reference in the second part of the VLOOKUP formula, I make sure to make the table reference absolute (hit the F4 key to automatically add dollar signs) so that even if I copy the formula, the reference to my table stays constant. Always design the table reference in step 2 so that the value you want Excel to search for is in the first column of the table.
Step 3: The third field tells Excel when it finds the value I’m looking for in the first field of the table which column has the information you want Excel to return. In the video, I have diagnosis codes in the first column, so that’s the column I use to look up the diagnosis code. The diagnosis description is in the second column, so I put a 2 in my formula. As Excel goes through the VLOOKUP formula for each cell, it finds the diagnosis code and then goes to column 2 to look up the field I want, the diagnosis description.
Step 4: Enter FALSE for now. We’ll cover the TRUE option in a future video. I didn’t worry about the fourth parameter in VLOOKUP for this video. We’ll focus on the difference between TRUE and FALSE in VLOOKUP after we’re a little more familiar with the function.
I hope this explanation coupled with the video is clear. Make sure you understand the general idea of VLOOKUP and we’ll add a few wrinkles to future VLOOKUP examples.