Step 37 of 40: VLOOKUP Function

This step illustrates how to use the VLOOKUP Function to indirectly reference data witin a table or a range.

Written description of the slides

1. Begin step 37 of 40 by clicking on the New Worksheet Button represented by + to the right of the last visible worksheet tab (or press Shift-F11).
2. Assign the name Waterfall Chart to the new worksheet.
3. Add the following captions:

Total Revenue

Total Expenses

Net Income

Make sure that you only enter say 'Total Revenue' as opposed to 'Total Revenue ' with a trailing space. Extra spaces in worksheet cells will cause VLOOKUP not to return a match. In this paragraph single quotes are for illustration purposes only and should not be typed out.

4. Type the formula =VLOOKUP(A1,'Financial Statements'!A$1:B$23,2,FALSE) in cell B1 and then copy it down to cell B3. Add a minus sign after the equal sign in cell B2. Amounts shown are placeholders and not check figures.

VLOOKUP breaks down as follows:

• lookup_value: A1 tells the formula what to look for.
• table_array: 'Financial Statements'!A$1:B$23 provides the cell coordinates of our list.
• col_index_num: 2 tells the formula in which column to return data from.
• [range_lookup]: 0 or FALSE indicates an exact match. Conversely, use 1 or TRUE if you want to return an approximate match.

5. Apply the Comma Style number format to cells B1:B3 and remove the decimal places.