Microsoft Excel: How to use Vlookup

Vlookup is one of the most useful functions built into excel.

Imagine the following situation. You are an HR professional and have an excel spreadsheet containing information on over 10,000 employees. Your task is to find addresses for a subset of 100 employees.

This task would be incredibly arduous if attempted manually, but a knowledgeable excel user can be complete in a matter of seconds.

I have simplified the lookup requested to just four names to make it simpler for demonstration purposes. It will work just the same no matter the number of item you need to lookup.

Vlookup searches for for a matched criteria in the left most column of table and returns the contents of a cell in the same row a set number of columns from the matched item.

I promise it is nowhere near as complicated as it sounds.

If there is no match in the lookup table excel will return the error "N/A".

The first step you need to complete is to create what I like to call a "lookup key".

VLookup can not handle criteria in multiple cells. In this case we need a cell containing both the First and Last Names of each employee to generate a match.

Begin by selecting column A and inserting a new column.

Next use concatenate to conjoin text in columns B & C with a space between.

=Concatenate(B2," ",C2)

Note: When using concatenate with text enclose it in quotes.

Copy the formula to the end of the data range.

The vlookup formula is composed of four elements.

• Lookup Value: This is the value you want to lookup in the list.
• Table Array: This is the range of cells for the lookup table you are using.
• Col_Index_Number: Beginning with the leftmost column in the lookup table equal to one count upward until you arrive at the column with the data you need.
• Match Type: "False" is to find an exact match. "True" is used to find the closest match. I always use false.

In this case our formula will be:

=VLOOKUP(H2,A:E,5,FALSE)

Copy the formula down to the end of the data range

Formatted cells can be problematic when using vlookup. If your formula is returning #N/A and you are sure there is an exact match in the lookup table. Try to change the format of both the table and the lookup value to general. Then copy and paste special values.