101 Ready-to-Use Excel Formulas

Chapter 6: Using Lookup Formulas

Finding data in a list or table is central to many Excel formulas. Excel provides several functions to assist in looking up data vertically, horizontally, from left to right, and from right to left. By nesting some of these functions, you can write a formula that looks up the correct data even after the layout of your table changes.

You can download the files for all the formulas at www.wiley.com/go/101excelformula.

Formula 55: Looking Up an Exact Value Based on a Left Lookup Column

Many tables are arranged so that the key piece of data, the data that makes a certain row unique, is in the far-left column. Although Excel has many lookup functions, VLOOKUP was designed for just that situation. Figure 6-1 shows a table of employees. You want to fill out a simplified paystub form by pulling the information from this table when an employee’s ID is selected.

Figure 6-1: A table of employee information.

The user will select an employee ID from a data validation list in cell L3. From that piece of data, the employee’s name, address, and other information will be pulled into the form. The formulas for the paystub form in Figure 6-2 are shown here:

Employee Name: =VLOOKUP($L$3,$B$3:$I$12,2,FALSE)Pay: =VLOOKUP($L$3,$B$3:$I$12,5,FALSE)/VLOOKUP($L$3,$B$3:$I$12,4,FALSE) ...