The website spreadsheets.about.com describes cell reference as identification of a cell or a group of cells in a worksheet. So data lookup will not work without cell referencing. There are 3 types of cell referencing, namely:
Relative cell reference: This is the default setting in spreadsheets. Row and Columns change with formula with this cell reference.
Absolute cell reference: This cell reference has instructions to peg a row or column. It is done by inserting the '$' sign in front row column notation. For instance $A1, will peg reference to column A, the cell reference will change only for row numbers.
Mixed cell reference: This reference combines relative and mixed cell reference to look up data for formula. To find out more on cell referencing , check this page
Formulas that look up data
There are so many formulas that look up data. It depends on what the user's objective is for the data. Is data the final product or work-in-progress that goes into another formula. This web page provides guidelines on how to decide what formula to use and offers tutorials on how to use these formulas. I will discuss the impact of cell referencing on a few of these formulas with an example.
Let's assume that there are two tables in a worksheet, the first table is just as shown below.
The other table will be looking up data from this table above. This table has got data validation tool that enables it to pick data from a list, this means that it can look up price data for the entire item on the list. It looks as shown below.
Any item picked from the list will have it's price displayed by looking it up from the list above. The lookups has been done with formulas VLOOKUP,INDEX MATCH and OFFSET MATCH. I'll be discussing the cell referencing used to look up the data.
VLOOKUP
The syntax for VLOOKUP can be summarized as shown below:
vlookup(what is to be looked up, where to search, what column to search, the kind of match needed)
In the formula above, E1 in blue is the item to be looked up, $A$2: $B$5 in red is range of cells to search, 2 means that search should narrowed to the 2nd column which is price, 0 tells the program that exact match is required. For this formula, mixed cell referencing has been used, the character in blue has relative cell reference, while those in red have absolute cell reference. This way, any item picked (having a relative cell reference) can be looked up because search boundaries have been set with absolute cell references in red.
INDEX MATCH
The formula combines two formulas to look up data and the syntax is as follows:
index(the result area,match(what is to be looked up, the area to search, the kind of match needed)
In the formula, the area in pink shows the boundary for the search, the character in red is the value to be searched for in the search boundary. The areas in blue is where the result data can be found. Like in the formula above, mixed cell referencing is used, the item to be searched has relative cell reference, while the result and search boundaries do have absolute cell referencing.
OFFSET MATCH
Just like the formula above, this formula is also a fusion of two formulas, the syntax is as follows:
offset(the first row of search area,match(what is to be looked up, other rows in the search area , match needed), the number of adjacent rows to return)
The the first row is pegged with absolute cell reference in blue, the item to be searched for has relative cell reference ( this way, any item can be looked up), the search area is pegged with an absolute reference in red.
Cell referencing establishes search boundaries for formulas to use during look up. The rule of the thumb is to have items to be looked with relative cell references, search areas with absolute cell references so that the search areas are confined and the right data looked up.
Enjoy looking up your data
No comments:
Post a Comment