Tips, Tricks, and Answers

The following articles are available for the 'Lookup and Reference Functions' topic. Click the article''s title (shown in bold) to see the associated article.

   Adjusting the VLOOKUP Function
The VLOOKUP function is very powerful, but it will only return values that meet a very limited set of criteria. If you want to adjust what the function returns, you may be out of luck unless you apply the techniques described in this tip.

   Calculating Future Workdays
Need to calculate the date that is a certain number of workdays in the future? You can do so using a couple of different worksheet functions, as described in this tip.

   Determining a State from an Area Code
Want to be able to take information that is in one cell and match it to data that is contained in a table within a worksheet? Excel's VLOOKUP function makes this a snap.

   Indirect References to a DSUM Parameter
Indirect references can be very helpful in formulas, but getting your head around how they work can sometimes be confusing. Here's an example of using an indirect reference within another formula.

   Looking Backward through a Data Table
Sometimes you need to look backward, through the information above your formula, to find the data you need. This can be done with a variety of formulaic techniques, as described in this tip.

   Looking Up Names when Key Values are Identical
Need to look up some values based upon some key items that may be identical to each other? Depending on the characteristics of your data, you may need to look at your worksheets just a bit differently than before.

   Making VLOOKUP Case Sensitive
The VLOOKUP function, like other lookup functions in Excel, is not case sensitive. In other words, it doesn't matter whether the characters being evaluated are upper- or lower-case. If you need the function to pay attention to character case, then you will need to devise a workaround. The techniques in this tip are a great place to start.

   Making VLOOKUP Trigger a Macro
VLOOKUP is an oft-used worksheet function to lookup values in a data table. If the function cannot return a value, it normally returns an error. This knowledge can help you figure out how to run a macro when VLOOKUP fails.

   Referring to the Last Cell
It is not unusual to use worksheets to collect information over time. As you keep adding information to the worksheet, you may need a reliable way to refer, in your formulas, to information in the last row of the worksheet. Here are some ways you can approach the need.

   Returning a Weight and a Date
If you have two columns containing dates and weights from those dates, you may want to pick a date associated with a given weight. It's easy to do using just a few Excel worksheet functions.

   Returning Blanks or Asterisks from a Lookup
Want to return more than a value when doing a lookup? Here's one way to do it by adding an IF clause to your formula.

   Returning Blanks with VLOOKUP
Normally the VLOOKUP function returns a value, and if it can't return a value it returns a zero. Here's how you can use the function within a formula to be more discriminating in what is returned.

   Returning Item Codes Instead of Item Names
The data validation capabilities of Excel are really handy when you want to limit what is put into a cell. However, you can't use data validation to return a value different than what the user selects. There are ways around this, though.

   Understanding the VLOOKUP Function
Functions are at the heart of Excel's power in working with data. One of the most misunderstood functions provided by Excel is VLOOKUP. Here's a brief overview of how to use the function.

   Using the COLUMN Function
Need to know the column number for use in a formula? The worksheet function you want is the COLUMN function, described in this tip.

   Using VLOOKUP to Access Information to the Left
One of the most useful function in Excel is VLOOKUP. One thing it won't do, however, is allow you to lookup information to the left of the index column. This tip examines a couple of ways you can get around this limitation by using other worksheet functions.