Demystifying the VLookUp function in Excel

Every time you go to your favorite restaurant, look at the menu for the item you want, and then jump over one column to check the price, you are doing a vertical lookup. You even do them in Excel!

You might open your customer list, look up a customer and copy that customer number to another spreadsheet. That’s a vertical lookup. The VLookUp function in Excel will do that work for you.

The easiest way to find the VLookUp function: Use the fx or Insert function dialog box, type in the word “lookup” and click Go. It will find VLookUp for you. Click on OK and you’ll see some cryptic argument fields. Here is what those argument fields mean in the dialog box.

  • lookup_value is asking WHAT do you want to look up?
  • table_array is asking WHERE do you want to look for it?
  • col_index_num is asking WHICH column number do you want back?
  • range_lookup is asking WHAT IF I can’t find it?

Here’s a really simple example to illustrate how it works. In a blank work sheet, number A1:A8 1 through 8. In B1:B8, type the names of eight colors. You want to be able to type in a color number in D1and see the corresponding color name pop up in E1.

The WHAT is color No. 3 and the WHERE is A1:B8. WHICH wants to know where the information is located: How many columns over from the lookup column is it? The name of the color is in the second column, so in this field, type a 2. (For the moment, let’s leave the WHAT IF part blank.) The result is blue. Now, type in another number and see another color name.

When WHAT IF is blank, you tell Excel if it cannot find the exact value, try to get close. So if you type in a 9, you get violet; a 3.1, you get blue; a 3.9, still blue. Excel got close without going over to the next number in the list.

When WHAT IF is FALSE (or a zero), you’re telling Excel that if it can’t find it, tell you! Excel does this by showing a #N/A for a no answer.

Tip: Using absolute cell references ($A$1:$B$8) or a named range en­­ables you to copy the formula down a column without worrying about it looking elsewhere for your WHERE values.