I Would Like To Teach the World VLOOKUP!

If I could impart one piece of wisdom to members of the corporate rat race it would be: Use VLOOKUP! I see minions hunched over their keyboards cutting and pasting or WORSE, Inputting data by HAND! This is 2008 and we need to let technology do the work for us, not the other way around!

VLOOKUP is the simplest way to quickly grab data out of a large data set. Its uses are truly endless, but a typcial corporate exmaple would be the catch phrase, "REPORTING."

The Example to the left contains a list of Retail outlets identified by a 5 digit code typical of most raw data in a corporate environment.

How to Use Vlookup:

1. Lookup_Value: You want to return a specific value from a list of data. In this example, you must indicate which retail outlet you are concerned with. You hardcode 23165 in cell A3.

2. Table_Array: This is the raw data. The number (or text) you are searching for MUST be in the first column of this array. In our example, the Outlet is what we are searching for and it is in column A. CAREFUL if your list contains numbers, make sure they are not a number stored as text. You simply need to reformat the cells as numbers to overcome this issue.

3. Col_Index: VLOOKUP will VERTICALLY search the first column of your Table_Array to find the value you are searching for. Excel defines the first column as column 1. If you indicate 1 for the Col_Index, VLOOKUP will return the value you are searching for IF it is actually in the list you are searching. In this example, we want to find the 1st Quarter Sales which are in column 4.

4. [Range_Lookup] When in doubt, specify FALSE. I would go one step further and say ALWAYS use FALSE. This value tells VLOOKUP if you want to find an exact match in the Table_Array, or just something that approximates what you are searching for. If you use FALSE and the Table_Array does not contain the value you are searching for, VLOOKUP returns an Error. If you use TRUE and the value is not in the data, VLOOKUP may find a value similar to what you are looking for and return that corresponding value. In other words, there are few, if any times the average Excel user would want to use TRUE.