Spreadsheets store data in a matrix-style format. You find specific data by pinpointing the intersection of two details. For instance, the spreadsheet below is a matrix of personnel and regions. If you want to determine the number of units Michaels sold in the West (39), you can run your finger down the Michaels column or across the West row until it intersects with the other point. Now, you probably know all that already. I’m absolutely sure that you know that using your finger to find data isn’t efficient or reliable!
There are a couple of complex solutions for satisfying this type of dynamic two-way lookup, but they’re too complex for me to remember. I always have to look them up to get the syntax just right. There’s nothing wrong with that, but there’s an easier solution—one that I (and you) can remember.
First, you need to add two validation lists to the sheet so users can specify both row and column values. To create a list of regions in cell B1, do the following:
- Select cell B1.
- Choose Validation from the Data menu to display the Data Validation dialog box. In Excel 2007 and 2010, choose Data Validation from the Data Validation dropdown in the Data Tools group on the Data tab. Or, press [Alt]+D+L.
- Choose List from the Allow dropdown.
- Enter =$A$6:$A$9 in the Source control.
- Click OK.
Next, create a validation list in cell B2 for the sales personnel listed in row 5, as follows:
- Select cell B2.
- Choose Validation from the Data menu. In Excel 2007 and 2010, choose Data Validation from the Data Validation dropdown in the Data Tools group on the Data tab. Or, press [Alt]+D+L.
- Choose List from the Allow dropdown.
- Enter =$B$5:$E$5 in the Source control.
- Click OK. At this point, you have two validations lists so users can select both a region and a name.
You still need a formula that can find the intersecting cells between the region and personnel. But first, the simple formula you’ll be using relies on range names—a range name for each name and each region. Now, that sounds pretty complex, but in truth, it’s easier than you might think. To quickly define a name for each region and name, do the following:
- Select the spreadsheet. In this case, that’s A5:E9 (don’t select the row of totals).
- From the Insert menu, choose Name and then select Create from the resulting submenu. In Excel 2007 and 2010, choose Create From Selection in the Define Names group on the Formulas tab. Or, press [Ctrl]+[Shift]+F3.
- The default settings in the Create Name dialog box, Top Row and Left Column, are what you want, so click OK without changing anything. (If that’s not what you get, try again—did you select both the row and column headers?)
With just a few clicks, you created eight ranges: North, South, East, West, Smith, Jones, Michaels, and Hancock. Now, you’re ready to enter the simple formula that pulls all the pieces together. In cell B3, enter the following formula:
Note, there’s a space between the two functions—that’s the Intersection operator. This operator returns the value in the cell(s) that’s common to the references. In this case, that means the formula will return the value that’s common to the references in cells B1 and B2—the region and personnel names, which happen to represent ranges! (The formula will return an error at first, but the operator isn’t the problem–it just needs values to evaluate).
Now, with everything in place, choose a region and a name from the two validation lists in B1 and B2, respectively. Below, you can see the result of choosing South and Hancock (55).
This formula evaluates as follows:
=INDIRECT(B1) INDIRECT(B2) =
=INDIRECT(South) INDIRECT(Hancock) =
Some of you might think this solution is convoluted because of all the steps, but for me, this solution is much simpler than a complex lookup formula. I can remember the pieces, but I can’t remember a complex formula that relies on several functions and very precise syntax. You’ll seldom (almost never) hear me say that my way is the best way to do anything but I prefer this solution because it’s easy to remember and so, for me, easy to implement.