Creating the right formula often seems like rocket science, but Excel offers many tools to help. One of those tools is the Lookup Wizard. You'll need a lookup formula to return a value from a table (the intersection of two values). Lookups can be difficult to write even if you know what you're doing. Don't struggle with the problem; launch Excel's Lookup Wizard.
If you don't find this helpful wizard on the Tools menu, install it as follows:
- Choose Add-Ins from the Tools menu.
- In the resulting dialog box, check Lookup Wizard and click OK.
In the worksheet, label two cells for entering two parameters, which represent the column and row headings. (You don't have to do this, but the labels make the process easier.) Then, you're ready to launch the wizard and create a lookup formula as follows:
- From the Tools menu, choose Lookup.
- Identify the table that contains the lookup values and then click Next.
- The wizard does a good job of identifying the appropriate column and row headings (the parameters). Most likely, you won't have to do a thing other than accept the wizard's choices and click Next.
- In the next window, decide whether you want to display parameters and the formula's results or just the results. If this is a one-time calculation, you don't need the parameters. If you want a more dynamic solution, include the parameters as shown. Then, click Next to continue.
- Specify a cell for the first parameter and click Next.
- Specify a cell for the second parameter and click Next.
- In the final window, select a cell for the formula and click Finish.
Everything's in place now. Simply update the parameters to find the value you need.
Pay close attention to the formula in the Formula Bar. How long would you spend writing and testing such a complex formula? I don't even bother. Excel is more efficient than I'll ever be!
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.