how to use vlookup in ms excel to add fields

For anyone who is serious about analysing data and creating pivot table reports, knowing how to use Excel's VLOOKUP function is a must. David Carter explains how.

When you export data from an accounts package, you will often find that essential fields are omitted and haven't come over into Excel.

All is not lost. You can pull in the missing data from a separate worksheet using Excel's VLOOKUP function.

If you want to analyse your accounts data in Excel via pivot tables, it is essential that you understand how to do a vlookup. The following simple tutorial shows how:

In the worked example below you have exported Sage Line 50’s Aged Debtors screen into Excel, but found it omits the customer’s name. So you will Vlookup in the name in from the customer file.

To work through the example, either follow the instructions below.

Next, download the files Aged debt.xls and Customers.xls. Open both of them in Excel.

1. In Excel, select the command Window-Arrange-Horizontal-OK. The screen splits into two – one file above the other.

2. Remove any blank rows in Row 2 (they will disable Sorts)

3. In Aged Debt.xls, insert a blank column to the right of column A, A/C. Give the column the heading "AcctName"

4. Click onto the first empty cell of the new column, eg B2.
From the menu, select: Insert-Function [or simply click the fx icon]

5. The Paste Function box appears. Check to see that VLOOKUP is in the right hand list. (If it is not, click Lookup and Reference in the left hand list, go to the bottom, select VLOOKUP)

6. Highlight VLOOKUP and click OK. The VLOOKUP box appears, starting with Lookup_Value.

Lookup_Value In Aged Debt.xls, highlight column A by clicking on the A at the top. (If the Vlookup box gets in the way, drag it away)

Moving lines appear around the “A/C� column and A:A appears in the Lookup_Value field.

Table_Array Click the mouse on to the Table-Array field.

In Customer.xls, click on the A at the head of the column, then drag to include columns B to F. Moving lines appear around columns A to F. [Customer.xls]Sheet1!$A:$F appears in the box

Col_ Index_Num 2 (i.e the number of the “Name� column B in Customer.xls)

At the bottom left hand of the box you should see Formula Result = A1 Design Services or whatever, meaning that you have filled in the values correctly. Click on: OK

You should see the customer’s name appear in cell B2. [If you don’t, but see =VLOOKUPA:A etc, you need to remove the formatting. Hit the Delete key, then Format-Cells-General]

7. Now copy the formula down into all the rows so that they all fill with names.

8. The column of names should be highlighted. Now select: Edit-Copy.

9. Then select Edit-Paste Special-Values-OK

You can now close Customer.xls. Maximise Aged Debt.xls, then Save.