1

EXCEL 2007 VLOOKUP FOR BUDGET

EXAMPLE

2

The primary reports used in the budgeting process, particularly for Financial

Review, are the Quarterly Financial Review Reports. These expense and revenue

reports are operating statements specifically set up for budget review.

A common question that is asked by those budgeting is, “How do I know what

object codes belong to which rows on the report?” This particular question

almost always arises regarding expenses such as Other Operating Expenses

(Internal/External) and Occupancy (Internal/External) among others.

In the example of the VLookup that follows, you will see how you can pull the row

labels on the Quarterly Financial Review Reports into your ADI budget/actual

worksheet based on the object code value.

For this example, we have two files.

The first file is the

qtrly_financial_review_object_code_and_report_line_mapping.xlsx file which is in

Excel 2007 but is also available in Excel 2003. The file contains the Object Code

Number in the first column, the Object Code Name in the second column and the

row label that the Object Code is associated with on the Financial Review Reports

in the third column (circled):

We will use this file as our source to bring the data from the 3rd column into our

budget worksheet using a VLookup.

3

To begin the VLookup exercise, open an ADI budget (or actual) worksheet. The

ADI budget (or actual) worksheet is the data file we’ll be using as the file that

we’re pulling the report row label into. We’re going to use an empty column in

Tools/Tips/Techniques Presentations: the worksheet and populate that column

with the row label of the report that corresponds with the object code in the ADI

budget (or actual) worksheet.

Next, open the qtrly_financial_review_object_code_and_report_line_mapping.xlsx file. This file is available on the Budget Forum website

(https://www.cmu.edu/finance/budget/budget-forum/index.html) under Tools/Tips/Techniques Presentations->Excel Tools. It’s also available on the Finance Divison’s Brown Bag Training page (https://www.cmu.edu/finance/training/brown-bag/index.html) under the Tips and Techniques - Using ADI and Excel Tools in Budget Preparation session.

VLookup Steps:

1. Open your ADI budget (or actual) worksheet. So that the file structure

remains intact for successful uploading into Oracle, do not insert a column

in the budget area of the worksheet. Rather, scroll to the right of the first

column outside of the template area – the first ‘blank’ column.

4

2. Give the column a heading. In this example, we’ll call it Financial Review.

3. Place the cursor in the first cell under the new column heading.

4. Open the tab on the Excel ribbon.

5. VLookup is an Excel Function. Excel functions are divided into categories in

the Function Library.

6. VLookup can be found in the Lookup & Reference category in the Function

Library. Click on Lookup & Reference.

5

7. Select VLookup (bottom of list) from the available functions.

8. The Function Arguments form will open.

a. Notice in the cell there is the beginning of a formula [=VLOOKUP()].

A function is simply an Excel formula in which you enter the

necessary arguments for the function to work.

6

b. The cursor is automatically placed into the first argument field,

Lookup_value.

c. In the center of the form is information about the function VLookup

and the argument you are about to enter.

9. The Lookup_value is the value that ties the two sets of data together.

a. The Object Code exists in the Budget Worksheet.

b. The Object Code also exists in the qtrly_financial_object_code file.

c. Notice also that the Object Code in both files is formatted as text –

even though the object code is actually a number. This is designated

by the little green triangle in the top left corner of the cell .

The value (Lookup_value) must be in the same format in both sets of

data for the VLookup to work.

7

10. To assign the Lookup_value to the formula, with the cursor still in the

Lookup_value field, click on the first Object Code in the Object Code

column in the Budget worksheet. (Note: this should be in the same row as

the VLookup.)

11. Click into the Table_array field.

12. The table array is simply the location of the data we want to retrieve. The

data can be in a separate file as it is in this example, or it can be on another

worksheet in the same workbook.

13. With the cursor in the Table_array field, switch to the

qtrly_fianancial_review_object code file by clicking on the Windows

Taskbar Tab .

A dotted line appears

around cell.

Location of the cell is

placed in the field.

Value in the cell is

displayed.

8

14. The Function Arguments form should still show. When assigning the table

array, remember the following:

a. The column with the Lookup value must be the first column in the

array, not necessarily the first column in the worksheet.

b. As mentioned before, the Lookup value in each file must have the

same format.

15. In this exercise, our Lookup value is in column A. Click on the column

designator, the letter A. The cursor becomes a black down arrow.

16. Hold the mouse down and move right to Column C. Column C contains the

data which we want to retrieve.

17. A dotted line appears around the data and Excel enters the data location

into the data array field.

9

18. Release the mouse.

19. Column one in the table array contains the Lookup value. Count the

number of columns from the lookup value to the desired data. In this

example, the data we want is in column three.

20. Click into the Col_index_num field.

1 2 3

10

21. You are immediately returned to the budget worksheet file.

22. Enter the number 3 in the Col_index_num field.

23. At this point you will know if the VLookup will be successful. Excel

previews the result for you (see circle in screenshot above).

24. Click into the Range_lookup field. The choices of entry are True (1), False

(0) or omitted.

a. True (1) or Omitted – if lookup value is not found in the table array, it

use the next largest value that is less than or equal to the lookup

value.

b. False (0) - looks for an exact match to the lookup value. If not found

then #N/A is returned.

25. We want an exact match so enter the number zero (0) or the word false

into the Range_lookup field.

11

26. Click on the button.

27. The first row label has been retrieved.

28. Look at the formula bar to see the calculation created using the arguments

entered.

29. The next step is to copy the formula down the column for all rows.

12

30. Now that you have the row labels in the budget worksheet, just filter on

the row label, for instance, Other Operating Expenses Internal to see

exactly what account strings are included in this particular row of the

Quarterly Financial Review Reports.