In Excel, use the function, VLookup, to lookup a value in one place and insert it into another. (For detailed instructions, refer to How to use VLookup in Excel (BB496418).

 

For example, you may have donations (invoice amounts, etc) for April and May in two separate Excel spreadsheets. You would like to be able to compare them side-by-side. To do this, you would use VLookup to lookup the constituents listed in the April spreadsheet to find the amount the constituent gave from the May spreadsheet. You want one spreadsheet to look like the following, which we will refer to as the 'final report':

 

An Excel spreadsheet that has a column for Constituent, April, and May

 

The easiest way to work with VLookup is to place the information on two different worksheets (e.g. tabs) in one spreadsheet rather than in two separate spreadsheets. In our example, we would have one spreadsheet with April on one worksheet and May on another:

 

First worksheet:

An Excel spreadsheet showing Constituent and April columns



 


Second worksheet:


An Excel spreadsheet showing Constituent and May columns

 

Excel considers the second worksheet, May, to be a table because you are looking up values from another spreadsheet (e.g. constituent name from Sheet 1) to find the appropriate value from the table (e.g. the donation amount from the May worksheet) and place it in the first spreadsheet (e.g. the amount in the May column for each constituent in the final report displayed above). This is helpful to remember when setting up the fields in VLookup.

 

The fields in VLookup consist of the following:

 
     
    • Look_up value: The value (constituent name from Sheet 1) that are looking up in the table (May worksheet) to find the needed informatin (amount given in May).
     
    • Table_array: The range of cells (e.g. rows and columns) from the worksheet that Excel considers the table (May worksheet). In the example listed above, the cell range is A2 to B4. You can either enter the cell range or name the cell range. (Note: If you use the cell range, you would need to enter the name of the worksheet and place a $ in front of the cell column and row so that it does not use relative values when copying it. For example: May!$A$2:$B$4.)
     
    • Col_index_num: This is the column from which the data is returned (Column B in the May worksheet). Convert this to a numeric value (2 because 'B' is the second letter of the alphabet).
     
    • Range_lookup: Enter 'True' if the values can be approximate or 'False' if the values must be exact. For example, when looking up Tracy Coers from the May worksheet, would you want to 'Traci Coers' to be the same as 'Tracy Coers'? If so, enter True for an approximate match. If not, enter False for an exact match.