Posted on March 18th, 2009 by Joseph Harris
The title of this post is slightly misleading. We will not be using vlookup to return the corresponding result of the last condition, but rather we will be mimicking vlookup to pull the last item.
Consider the following data:
George
Smith
Tom
Thompson
George
Kenny
Dan
Jackson
Sally
Richards
George
Daniels
A vlookup of the name George will return Smith, What if you want to lookup the last George listed? The [...]
Read More
Posted on March 13th, 2009 by Joseph Harris
There are many times when you may have a lot of data that does not look right when graphed all together. 300 data points is probably too many for a small chart. So what is the solution…scrolling charts! I will show you in this article how to create a scrolling chart without using dynamic ranges. [...]
Read More
Posted on March 3rd, 2009 by Joseph Harris
The #VALUE error is an error that is seen almost as often as #N/A. In short, the VALUE error occurs when a formula refers to a cell that contains a different than expected format. In this article, I will discuss the main causes of #VALUE! and some possible solutions.
The top 3 causes of #VALUE! error [...]
Read More
Posted on February 17th, 2009 by Joseph Harris
The #N/A error happens most often when a lookup function cannot find a result. You can manually remove the all of your #N/As or you can stop them in your original formula. A simple and effective way of trapping the #N/A errors and replacing them with whatever you would like is to use the ISNA() [...]
Read More
Posted on February 16th, 2009 by Joseph Harris
One of the most time consuming parts of producing a scheduled report is updating all of the charts in the report. Many times, an analyst only wants to show the last 7 days or the last 6 months of data and not the entire data set. To achieve this, the analyst changes the the Y [...]
Read More
Posted on February 9th, 2009 by Joseph Harris
The standard vlookup function allows for one condition to be looked up. For example, if I have the data in rows 1 through 7 below, and I want to determine the favorite color of Dan, who was born in Jan and is 16 years old, the standard vlookup function will not work.
The standard vlookup function [...]
Read More
