Lookup and Reference Functions

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.

Exiting a For ... Next Loop Early
If you use For ... Next loops in your macros, make sure you give a way to jump out of the loop early. That way you can limit the amount of time spent by the macro in the loop and speed up performance.

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 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's some ways you can approach the need.

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.

Understanding the VLOOKUP Function
Need to look up information in a data table based on what is in the first column of that table? The worksheet function of choice in such situations is VLOOKUP, described in this tip.

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.