Vlookup( )

1. First, the lookup table was named SizeTable …using the Name box Vlookup( ) The Vlookup( ) formula allows you to look up entries in a table located on your spreadsheet. Before you build the formula, it is helpful to plan the formula’s component parts (such as naming the table). Press Page Down to play this presentation

2. The Function toolbar button delivers a list of Excel’s built-in formulas Vlookup( ) is listed with the Lookup & Reference functions Vlookup( ) Now that your sheet layout is designed and the appropriate ranges are named, you can begin to build the Vlookup( ) formula. Please note: range names are not required, but they can be very helpful. Press Page Down to play this presentation

3. Enter the location of the value to be looked up. Choosing Vlookup( ) brings up the formula’s dialog box Enter the location or name of the lookup table. Enter the column number identifying the values that you wish the formula to retrieve. Vlookup( ) Completing the Vlookup( ) formula is largely a matter of knowing how to fill out its dialog box. Note how hard Excel tries to tell you what is going on: don’t forget to read the screen. This dialog box is completed for this example: Press Page Down to play this presentation

4. Vlookup( ) The final Vlookup( ) formula refers to the input cell, the lookup table, and the lookup column within the table. Build this sheet yourself, and see how cell B4 changes when you put 20, 30, 49, 65, and then 19 in cell B2. The resulting formula looks like this Press Page Down to play this presentation

5. The Vlookup( ) dialog box requires at minimum the following entries: Lookup_value: this is the number that you want to use as input for locating your position in the first column of the lookup table. Table_array: this is the range that holds the lookup table. You can refer to the range either by spreadsheet coordinates or, of course, by using a range name. Col_index_num: this is the number of the column in the lookup table where you want Excel to find the value to return for your answer. Range_lookup: this is optional. Use it only if you wish your lookup table to force an exact match in the input column. Building the Vlookup( ) formula The following slides walk through building the Vlookup( ) formula step by step. They use the SizeTable example.

6. The Windowshade The dialog box often covers the area of the spreadsheet that you wish to refer to when building the formula. You can, of course, move it out of the way. If you are using a small screen (on a laptop, for example), you may find this windowshade button helpful. When you click here, the dialog box will roll up out of the way, allowing you to build the formula by clicking on spreadsheet locations that are currently covered. For example, press Page Down to see what happens when you click this button before entering the Lookup_value. Building the Vlookup( ) formula

7. The Windowshade When you press the windowshade control, the dialog box rolls up so that you can see the spreadsheet underneath. To expand the dialog box to its previous size, click on the windowshade button again. Press Page Down to see what happens. Building the Vlookup( ) formula With the windowshade up, you can select areas on the sheet -- and the compressed dialog box will still pick them up. Here, we clicked on cell B3. The dialog box automatically picked up the location.

8. The dialog box will reappear as the windowshade unrolls, with the B3 entry still intact. The remainder of this slide illustrates the entries for the remainder of the formula. Note how the explanatory text at the bottom of the dialog box tries to tell you what is going on, and how the dialog box attempts to show you what pieces of the formula it thinks it is looking at (including the formula’s result). Enter the table name by typing it. You could also roll up the dialog box and select the table’s region on the spreadsheet manually if you had not named the range earlier. Note how the dialog box adjusts to show you the range it thinks it found. Column 1 Column 2 Set the formula to lookup the value in the second column, e.g. the size column. The waist size column of the lookup table is column 1. Note that the dialog box tries to tell you what it thinks the result of the formula is. Building the Vlookup( ) formula

9. Building the Vlookup( ) formula The final Vlookup( ) formula refers to the input cell, the lookup table, and the lookup column within the table. Build this sheet yourself, and see how cell B4 changes when you put 20, 30, 49, 65, and then 19 in cell B2.