Software

Easy lookup formulas in Excel

Spend hours coming up with a complex formula, or let Excel work through the problem in just a few minutes.

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:

  1. Choose Add-Ins from the Tools menu.
  2. In the resulting dialog box, check Lookup Wizard and click OK.

october2008officeblog1figar.jpg

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:

  1. From the Tools menu, choose Lookup.
  2. Identify the table that contains the lookup values and then click Next.

october2008officeblog1figbr.jpg

  1. 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.

october2008officeblog1figcr.jpg

  1. 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.

october2008officeblog1figdr.jpg

  1. Specify a cell for the first parameter and click Next.

october2008officeblog1figer.jpg

  1. Specify a cell for the second parameter and click Next.

october2008officeblog1figfr.jpg

  1. In the final window, select a cell for the formula and click Finish.

october2008officeblog1figgr.jpg

Everything's in place now. Simply update the parameters to find the value you need.

october2008officeblogfighr.jpg

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!

About

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.

10 comments
cabanks
cabanks

Excel 2007 - where do I look for this tool?

dogknees
dogknees

It's a useful technique, but I always get nervous when people use a tool without understanding what's going on. If for example, the formula gave incorrect results. The person wouldn't know they were wrong, or even if this was obvious, wouldn't know how to fix it. In the example you gave if there is no match found for one or more of the lookups, you'll get the wrong answers with no real clue as to why it's wrong. I'd recommend when demonstrating a shortcut you at least explain the formula that was created. That way the person has some idea of what they're looking at, and they may find the explanation useful when they come across a similar problem. Regards PS Believe me, that is not a "complicated" formula! It's a simple one.

mpiencenaves
mpiencenaves

I read your notes on ms excel and its a great help.Being unfamiliar with excel and its formula, it helps to understand how it works. Your discussion got me very interested for further training to learn everything. I'll be using it for data entry jobs I'm planning to apply for. I am also going to enroll in an online training course for ms excel. I found this website, simonsezIT.com and the course offering is very comprehensive. Aside from excel, it also offers MS Word, Powerpoint 2007 and Outlook. They're opening a Vista training too. Prices are very affordable, from $12/month. I think the annual fee is even lower. $70/year. I think in this era, everybody needs to be proficient in these areas, especially if we want to enter into the online business. It is definitely an edge

AtCollege
AtCollege

Click the Formulas tab. At the end of the ribbon, look for the Solutions group. Click Lookup.

MongoMe
MongoMe

I have to agree. For first time users, it's a great form feed for vlookup or hlookup. However there are limitations. I work with large tables all the time so working with the lookup formulas are easy. What this form (and the corresponding formulas) fall short on is finding the right answer. What they do best is finding the first match. If there is a situation where there may be more than one entry against a particular reference, it will only display the first one it comes across. For a new user, or one not familiar with the limitations of this form/formula, it could cause some large headaches down the road. It is by no means perfect but if the user is aware of what it can and cannot do, then it is a great tool instead of a crutch.

ssharkins
ssharkins

I just tested the formala again by removing a value -- the lookup returned $0.00 -- what error are you talking about? Can you give me a specific example to test? If you're right, I certainly want to warn readers about that possibility.

ssharkins
ssharkins

I'm glad you found the information helpful. The good news is that Office applications are easy to learn, but experience is the key. You learn things as you go -- I'm still picking up little tidbits and I've been using Office since it was introduced! And of course, it changes all the time. I'm not convinced the wizard created the most efficient formula, but that's not important to me. Knowing how to move from one task to another quickly is more important than perfecting individual formulas. Even if I could've written that formula in my head, without much effort, I couldn't have typed it in correctly! :) I know there are gurus out there who can work with that kind of expertise but most of us don't.

dogknees
dogknees

Did you remove a row from one of the lookup tables? Or alter the data so there's no match for one or more rows?

mpiencenaves
mpiencenaves

You quite right actually. I'm a little proficient with Ms Word but Excel stumps me all the time. However, I need to learn it fast as I am doing work online and its the tool for data entry. Hopefully, I'll be able to get the hang of it.

ssharkins
ssharkins

I deleted a single value and then plugged in the parameters for that cell -- the formula returned $0.00, as I expected, and not an error. Now, if you're talking about removing a parameter label or the user entering a parameter value that's incorrect (typo) I can see that generating an error.

Editor's Picks