Vertical Lookup

 

A vertical lookup is an extremely useful – but little-used – feature of Excel. It allows you to link data arranged in a table to data in your spreadsheet by matching a field contained in both the table and the spreadsheet – an id number, a last name, etc.

 

For example, say you have a spreadsheet that lists your employees and their home addresses, and another spreadsheet that lists your employees and their work departments. Now suppose you wanted to put that information together – have one spreadsheet containing the home address AND the department information. Vlookup will use a field that both spreadsheets have - in this case the employee name, look the information (the department) up in the second spreadsheet and display that information (the department) in the first spreadsheet.

Here’s how to do it:

 

Copy the second spreadsheet onto a worksheet in the first spreadsheet, so that the information is all in the one Excel file, but on different worksheets (tabs).  Sort the data in ascending order by the field the two lists have in common - in this example, the name.

 

 

 

On the first worksheet (with the employee name and address), type in a header for Department:

In the cell under the header, type in the formula for vlookup:

=VLOOKUP(lookup_value,table_array, col_index_num,[rage_lookup])

 

Look_up value is the cell address of the field the two worksheets have in common – in this case, the employee name (A2).

 

Type a comma (very important!)

Table_array is the table that contains the department name for the employees – in this example, you’ve placed it on another worksheet (tab) in this file. To enter this information into the formula, go to the worksheet the table is on and select the entire table (you can hold the mouse button down and run the cursor it over the table: Excel will enter it automatically). 

 

Type a comma (very important!)

Col_index_num is the number of the column in the table that contains the information you are looking for. In this case it’s “2” because the department information is in the second column of the table.

 

Type a comma (very important!)

Type FALSE. This tells Excel to return a department from the table only if the name is an exact match. If you were to type TRUE, it would return the next entry in the table if it didn’t find an exact match for the name.

 

Type a close parenthesis ) to end the formula, and press Enter. You will be returned to the first worksheet.

Now type a $ in front of the letter and the number part of the cell addresses for the table array. For example, A1:B19 would become $A$1:$B$19.

 

 

 

The cell containing the vlookup formula will now display the department for the employee. Copy the formula down for the rest of the employees.

You’ve now successfully combined the information from the two separate sheets onto one sheet, linked by the employee’s name.