Excel: VLookUp

Creating a list lookup.

Many Excel spreadsheets contain data spread across many different worksheets and in collaboratively work environments, worksheets are shared among coworkers.
The result of this data separation is that sometimes a scenarios arises when critical data is needed but unavailable by the current spreadsheet.
To overcome this limitation, I will use the Excel VLookup formula to compare a key field on one worksheet against another key value in a separate worksheet.
First, I’ll begin with simple Sales worksheet containing three orders (1-3).
Notice the column “Sale Associate” which references another worksheet.

2-excel-vlookup

The second worksheet I have is the actual lookup used to retrieve the names of Sales Associates.
Ensure this worksheet’s key column (the values used to as a reference by the first worksheet) is the first column.
First, I’ll insert a new formula by searching for VLookup.

3-excel-vlookupOnce found, I’m prompted by three required and one optional fields.

4-excel-vlookupFor the first argument, I’ll specify I want the key lookup fields to be the “Sales Person ID” value (not column).
Next, I’ll click inside the second argument and select the “Sales Associates” worksheet tab and highlight all available cell values excluding column headers.

5-excel-vlookup

Finally, for the third argument indicating which column’s data to retrieve, I’ll enter ‘2’ for the second “Name” column in the worksheet.

6-excel-vlookup

Upon pressing [ENTER] the first sales name is listed in the first worksheet by pulling it from the second worksheet.

7-excel-vlookup

To implement the lookup on the other Sales Associate values, I’ll simply drag/copy the cell’s formula to the other two cells.

8-excel-vlookup

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s