The VLOOKUP Function is one of the most useful functions in Excel, and is a fast way to look up a value when your information is arranged in rows and columns. The VLOOKUP function has four arguments (values in the function separated by commas) and because of this people often think VLOOKUP is complex. Once you try a few practice problems with VLOOKUP you'll see how simple they really are.
If your data is arranged in a table with rows and columns, the VLOOKUP function will tell you a value in one row if you know a different value to the left in that same row. For example, say you have a table with columns for employee ID, hire date, and employee name. Each row represents a different employee. Say you want to retrieve the name of the employee with ID 1234.
The syntax of VLOOKUP is as follows:
Note that the value you know (the first argument) must always be on the far left side of the range you choose.
Excel lets you use the VLOOKUP function with wildcards. Note that this only works in exact matching mode, so your fourth argument must be 0 or FALSE. Also note that this only works for matching strings of text.
To use a wildcard in Excel, you concatenate an asterisk enclosed in quotes at the beginning or end (or both) of your string of text. It looks like this:
This will return the value in the first row with text that begins with "My text".
Suppose our columns were in a different order, with Employee name, employee ID, and hire date from left to right.
|1||Employee Name||Employee ID||Hire Date|
|3||John C. Reilly||1020||10/8/1992|
|4||Sacha Baron Cohen||1234||12/10/2005|
Say we want to find the ID number of the employee whose first name is "Amy". We could use the wildcard match as follows:
This will return the ID number of the first employee whose name starts with "Amy". Now suppose you want to find the ID of the employee whose middle initial is C. You can use wildcard at the beginning and end of the search string as follows:
This will return the ID of the first employee who has "C." in their name.
Excel lets you use VLOOKUP to search for an exact match or an approximate match, as determined by the fourth argument, which is optional. VLOOKUP defaults to approximate match, so if you omit the fourth argument, or if you include it as TRUE or 1, then VLOOKUP will use an approximate match.
If you include the fourth argument as 0 or FALSE, then VLOOKUP will use an exact match.
Exact matching makes sense when you're searching for an exact value, like Employee ID number in the examples above. If you don't match on the exact ID number, you may get the wrong employee!
If you're not sure whether the exact value appears in the list, you can use approximate match to find the value that matches best. If Excel does find an exact match, then it will match on that value. If it does not find an exact match, it will find the closest value to match.
If VLOOKUP is using an approximate match it will use an exact match if the exact value is available. If not, it will use the next smallest value, so if you're using an approximate match in Excel you need to make sure the far left column is sorted from small to large, or alphabetically.
As with all functions in Excel, the best way to improve is to try as many hands-on practice exercises as possible. The more repetitions you get, the more quickly and naturally you'll be able to use them in your everyday work.
Try some Excel Exercises with the VLOOKUP function now!