Demystifying VLookUp in Excel — Business Management Daily: Free Reports on Human Resources, Employment Law, Office Management, Office Communication, Office Technology and Small Business Tax Business Management Daily
  • LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+

Demystifying VLookUp in Excel

Get PDF file

by on
in The Office Tech Pro

Let’s say you’re stopping off for a fast food lunch. You know exactly what you want, but not how much it costs. So, you look up at the menu, locate the item and then look one column over to the right for the price. A vertical look-up or VLookUp function works in precisely the same way. You know:

- WHAT you want to look up,
- WHERE you need to look for it, and
- WHICH column has the data you need.
- WHAT IF you can’t find it? Either you’ll pick something close, or decide that you don’t want anything.

In a VLookUp function, the field named Lookup_value is actually WHAT you are looking up. The Table_array is WHERE to look for the information you want. Col_index_num is WHICH column has the data. Finally, Range_lookup is the answer to WHAT IF it is not found.

In our sample spreadsheet we are looking up a description and price based on an item number.
- WHAT is the item number,
- WHERE is our products table which is in the range A2:C26,
- WHICH is 2, the product’s description which is in the second column.
- And, since we don’t want to get the price of the wrong item, our WHAT IF says FALSE. This tells Excel to find the exact value or report back that it cannot find it.

The second function would only differ by the WHICH, since the price is in the third column.

Some Gotchas
1. Using AutoFill to copy the formula down would have altered the arguments to F3 and A3:C27, for Lookup_value and Table_array, respectively. Use either a named range or absolute cell references to avoid the problem.
2. Notice the the WHICH or Col_index_num is always a number. To arrive at the number, start at the look up column; count it as one; then move to the right, counting columns until you arrive at the data you want the VLookUp to return.
3. A blank in Range_lookup means the answer to WHAT IF I can’t find it, is “Get close without going over to the next list item.” This works for things like commission calculations, but not so well for item number lookups.

Leave a Comment

Previous post:

Next post: