In Excel, use the function, VLookup, to lookup a value in one place and insert it into another. For a detailed explanation, refer to What is VLookup in Excel and when would you use it? (BB497426).The instructions below use the following example:
You have a spreadsheet with April's donations and a spreadsheet with May's donations. You would like a spreadsheet with April's donations in one column and May's in another that looks like this:
To follow along with the instructions, download the VLookupSample.zip.
- Open April and May's spreadsheet in Excel.
- Add a second worksheet (e.g. tab) to Excel and name it "May". Copy and paste the information from May's spreadsheet to the "May" worksheet.
Note: VLookup does not require the information to be in separate worksheets in one spreadsheet. However, this is the easiest way to understand it and work with it, so the example uses it this way.
You now have a spreadsheet that looks like this:
In this case, we want the information from the second worksheet to appear in column B on the first spreadsheet. This means we are using a value from the first worksheet (the constituent's name: Column A) and looking up information from the second worksheet (May's donation: Column B). Excel considers the worksheet from which you are looking up information (e.g. May, the second worksheet) to be a lookup table. The table array is cells A2 to B4, which is shown in highlights in the screenshot below.
- Name the group of cells in the table array, which makes it easier to use in the VLookup function. To do this:
- Highlight the cells as shown in the screenshot above.
- Select Insert, Name, Define from the menu bar.
- In the Names in workbook field, enter MayTable and click OK.
- Sort the May worksheet by column A (Constituent) in Ascending order. This is required for the VLookup function to work correctly.
- In the main worksheet (Sheet1 in the example), place your cursor in the first cell in which you want to place information. In this case, it is the first cell in which you will place May's data:
- Select Insert, Function from the menu bar.
- In the Insert Function screen, enter VLookup in the "Search for a function" text box and click Go. In the "Select a function" box, highlight VLOOKUP and click OK.
- The Function Arguments screen appears.
- In the Lookup_value field, enter the cell value you want to look up in the table array (e.g. May worksheet). In this case, it is the first constituent's name, Tracy Coers, from Sheet 1. The cell is A2. (You can also click in cell A2 and Excel will automatically enter the value of A2.) Press the Tab key.
- In the Table_array field, enter the table array explained in step 2. The table array consists of the cells named in step 3: MayTable. Press the Tab key.
- In the Col_index_num, enter the numeric value for the name of the column containing the information to lookup. In the example, column B on the "May" worksheet contains the information we are looking up. The numeric value for "B" is '2' because "B" is the second letter in the alphabet. Therefore, you would enter 2.
- In the Range_lookup field, enter true to use a close match when comparing the lookup_value (the constituent name) in the table array (MayTable). Enter false for an exact match. Because we want the constituent name to be exactly the same in both places, enter false.
- Click OK.
- If there is an associated value, it will now appear in your main worksheet (Sheet1). Otherwise, you receive an #N/A which means that there is no value in the second worksheet.
- Copy this value all the way down the column.
- Because the VLookup will recalculate everytime you make a change to the spreadsheet, it is best to get rid of the formula. To do this:
- Highlight the column by clicking the column heading. In this case, click C.
- Press Ctrl+C to copy the column.
- Place your cursor in the first cell in the next column. In this case, cell D1.
- Right-click and select Paste Special. In the Paste Special screen, select Values and click OK.
- Delete the column with the VLookup formulas. In this case, D.
- Do a Find and Replace and replace #N/A with the appropriate value. In this case, 0.
- Add the headers and formatting you want.