Excel's Amazing VLOOKUP Function

Great Computer Tips

Excel's Amazing LOOKUP Functions

The LOOKUP functions in Excel are so powerful but so confusing to many. Part of the problem is the terms that Excel uses when defining the arguments. Let's see if I can make things clear for you.

LOOKUP functions copy information from a table based on data in specified cells. You see LOOKUP functions in use all of the time. The bar code scanner in stores uses a form of lookup. The scanner identifies the UPC number and returns the name of the item and the price from a list stored on the server. The UPC is the trigger or the number being looked up and the price is in one column and the name of the item is stored in another column. I can hear you saying that you're not selling products and don't use UPC numbers. Another example of a lookup is auto-entering customer information using a customer number.

This VLOOKUP uses an Item # that is common to the worksheet and the Lookup Table to automatically import information.

For many years I was in charge of scoring sailboat races and we used an Excel worksheet to accomplish this. When a boat came out to race we entered the sail number and the spreadsheet looked up information from another sheet that had hundreds of sailboats listed. The sail number would trigger the lookup and the name of the boat would be automatically entered as well as the name of the skipper and the handicap.

In a recent tip for parsing date information into individual segments, I suggested the use of the WEEKDAY function to determine the day of the week that a particular day falls on. The result of this formula is a number from 1 to 7. If you wanted to convert those numbers to day names (Sun, Mon, etc.) then you would consider using a lookup to accomplish this task.

So now that you have some ideas about how you would use a lookup let's get started.

Setting up the Lookup table
The data in the table needs to be set up so that the lookup number (the information you are looking for such as the UPC code or model number) is in the leftmost column and is sorted in ascending order (from smallest to largest). To make it easier to identify the location of the table the next step would be to name the table area.

Naming cells
Cells already have names such as A1, B27, etc. but you can also give them names to make it easier to reference that specific cell, making the cell address (name) an absolute reference rather than the default relative address. In the image above you would want to name cells A1 through D10 but because you might be adding to your product list we'll give columns A through D a name.

• Start by selecting the cells or columns you want to name.
• Click in the name box (the name box is in the upper left side of your screen, just above the first column, see image to the right) and type a name for your cells.
• Press the Enter/Return key. This step is very important.

If we called this area of our worksheet "table" we would be able to use the word table in our formula and Excel would know that we were referencing these cells on this worksheet. Note: When naming cells you need to remember that you can not use spaces and the name can not begin with a number.

Inserting the LOOKUP Function

Now that we've got the data range named lets look at how to construct the VLOOKUP function. Look at the image below, we will be inserting information in the worksheet on the left by looking it up in the sheet on the right.

In the example we will create a function to look up the description of an item based on the Item #. In the first cell in the description field, choose VLOOKUP from the Formulas tab, Function Library group, Lookup & Reference option. We are using a VLOOKUP rather than a HLOOKUP because the data we're looking up from in the worksheet on the right  is structured in a vertical format.

• In the function builder dialog box put in the cell address of the trigger cell. In this case it is cell B20, the cell that will contain the item #. 
• In the second field type the name that you gave your look up information. I named mine "table" so that's what I typed in here. 
• In the third box identify the column in the worksheet on the right that has the information you want returned. The Code (Item #)  is column 1, Description is column 2, Price is column 3 and Color is column 4. In this case we would type a 2. 
• If it is important, as it is in this case, that if there is no exact match to the Item # to return an error code, then in the fourth box enter the word "False" in the fourth field. When finished click OK.

That's it! Change the Item # and see if the results change. If it all looks good create the VLOOKUP function in the other cells.

You may notice that I refined the formula a little so that the formula didn't return an error message when the Item # cell was blank. You can see how this formula is structured by looking on the formula bar in the picture above.

Because this is a little complicated, I created a video and posted it on YouTube. Click here to see the video.

Happy Computing!

Diane

Don't forget to click the Subscribe to 100 Computer Tips in 100 Days via email link on the right side of the screen so you get email alerts when Diane adds a new post.

Want more tech news or interesting links? You'll get plenty of both if you keep up with Diane McKeever, the writer of 100 Computer Tips in 100 Days, by subscribing to her Facebook posts or visiting her web page, www.dianemckeever.com .