Wednesday March 12, 2014
Excel VLOOKUP Tutorial
© Ted French
VLOOKUP is probably the best known and most used of Excel's lookup functions.
What it can be used for is to locate individual fields of information in a table of data or database .
For example, given the name of an item, VLOOKUP can return the' price of the item or the number of these items in stock.
This information could then be used to fill in fields in a form or to complete a report.
To find out all the details on using this function, read the Excel VLOOKUP Step by Step Tutorial.
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.