Topic: Lookup and Reference Functions

The following articles are available. Click the article's title (shown in bold) to see the associated article.

Adjusting the VLOOKUP Function The VLOOKUP function returns either an exact match with a lookup value or, if one is not available, the first value that is less than the lookup value. If you want to instead find the equal match or the first value greater than the lookup value, then you need to use one of the approaches described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Calculating Future Workdays Excel makes doing math with dates quite easy. This tip starts with the simplest of date math—adding a few days to a date. Things get more interesting when you want to calculate only weekdays or work days, but this tip explains how you can conquer that need, as well. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Exiting a For ... Next Loop Early One of the most common programming structures used in macros is the For … Next loop. This structure is used to repeat a series of programming statements any number of times. You can jump out of the structure early by using the Exit For statement, which is described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Indirect References to a DSUM Parameter DSUM, like most other Excel worksheet functions, requires parameters that define how the function does its work. If you don’t provide those parameters in the manner Excel expects, then you can get errors or incorrect results. This tip explains how you can overcome one such error to indirectly reference the data you want summed. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Looking Up Names when Key Values are Identical Do you have a list in which there are duplicates in the key field, and you want to look up non-duplicate values associated with those keys? This type of problem can be more common than most people think, and it is definitely more vexing than most problems. This tip explains several ways you can find the information you need from your list. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Referring to the Last Cell If you have lots of data in a table, and the length of that table can vary, how do you reference the last cells in each column of the table? This tip explains several ways that you can achieve this common task. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Returning Blanks with VLOOKUP By default, VLOOKUP returns a 0 if the source cell contains either 0 or is empty. In some situations you may want to differentiate between actual zero values and blanks. This tip describes three ways you can make the determination. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Using the COLUMN Function The COLUMN worksheet function returns the column number of whatever column it appears within. This can be handy for some formulas, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Tips, Tricks, and Answers

Analyze Mega Data!

Helpful Links

Great Info!

RSS Feeds