Pinpoint data in an overwhelming data table no formulas necessary!
Pinpoint data in an overwhelming data table — no formulas necessary!
by
Application:
Microsoft Excel 2000/2002/2003/2004/2007
Operating Systems:
Microsoft Windows, Macintosh
If you’ve ever tried to locate a row and a column’s intersecting cell in a table that spans several screens, you know how much it can strain your eyes — and your patience. You could come up with a lengthy look formula, but we’ll show you an easier solution. Utilize Excel’s Lookup Wizard to create a search engine that eliminates needless scrolling.
To build a helpful interface that works like an Internet search engine, we’ll:
• Install the Lookup Wizard, which is an Excel add-in.
• Walk through the Lookup Wizard’s steps with a sample scenario.
• Demonstrate the new search engine’s power.
Between scrolling left and right, and then scrolling up and down, searching a huge data table is often inefficient and cumbersome. And of course, there’s always a chance for human error. You could use Excel’s lookup functions to create a formula for seeking out the intersecting cell, but in some cases, you’d need a combination of several of these functions nested within others. Instead, with only a few simple steps, Excel’s Lookup Wizard can help you create your own search engine for an exceptionally large table. You’ll never need to squint and scroll again!
Look it up in a snap
The Lookup Wizard generates a complex formula using a combination of several Excel lookup functions. The formula will drive the search engine that allows you to search extensive worksheets.
For example, let’s say you analyze direct-mail marketing campaigns for three branches of the company. As you can imagine, your table gets larger and larger as you add new campaigns. Now, when you want to find the gross profit for the 109A campaign, you’ll have a hard time finding the correct value. We’ll show you how to create a simple three-cell search engine, as shown in Figure A, so you can input a campaign ID and data analysis stat to quickly find the correct value without digging through several dozen rows and columns. Here’s how the Lookup Wizard can do it.
A:
The Lookup Wizard helps you create a dynamic search engine for your worksheet.
Note: For demonstration purposes, we’ve kept our sample worksheet small, but this technique is better suited to worksheets with several dozen [...]
