Monday March 10, 2014
VLOOKUP is one of Excel's better known lookup functions.
It is limited, however, to using a single lookup criterion when trying to find information in a data table.
We can get around this limitation by using the INDEX and MATCH functions together to create a lookup formula that allows multiple criteria to be used when finding the data we are after.
So instead of finding items by name only, using multiple lookup criteria would allow you, for example, to find those with a certain name that were purchased from a specific supplier, or are a certain color.
To find out more read the step by step tutorial on Lookup Formula with Multiple Criteria in Excel.
Thursday March 6, 2014
The OFFSET function can be used to return the value of a second cell that is a specific number of rows and columns away from a cell referenced in an adjacent range.
By itself, the OFFSET function, like many other Excel functions, can have limited uses. Its value lies in combining it with other functions to create flexible lookup formulas.
For example, by combining the SUM and OFFSET functions, a dynamic range formula can be created that allows the SUM function to automatically accommodate new cells of data added to the range.
To find out all the details about this function, read this short tutorial on the OFFSET function.
Tuesday March 4, 2014
By combining the INDIRECT function
with the SUM function, you can create a formula that makes it easy to adjust the range of cells added together in a worksheet.
The SUM function, of course, does the adding, while INDIRECT takes care of the dynamic range part.
What the INDIRECT function does is allow you to change the start and end points of the formula's range without editing the formula itself.
To find out all the details, read this tutorial on the Excel SUM - INDIRECT Formula.
Sunday March 2, 2014
Excel INDIRECT Function
© Ted French
The INDIRECT function, as its name suggests, can be used to indirectly reference a cell in a worksheet.
This is done by entering a cell reference as text string into the cell that is being read by the function.
INDIRECT converts this text string into a real cell reference, and then reads and displays the contents of this new cell.
Like many other Excel functions, on its own INDIRECT has limited usefulness. It is most effective when combined with other functions to create more complex formulas.
INDIRECT can be used with a number of functions that accept a cell reference as an argument, such as the SUM Function.
By combining the two, you can create a formula that lets you dynamically adjust the range of the SUM function without having to edit the formula itself.
To find out all the details of using this function, read this tutorial on the Excel INDIRECT Function.