|
Row matching is possible as well as column matching
In Lesson F we saw that when confronted by a formula that mentions several cells where one would be more usual, Excel can often make sense of the expression. It will resort to column matching to pick out one of the cells to act on.
The process Excel goes through to work out which cell to pick from a range doesn't only involve column matching. It can involve row matching too.
Example of column matching
We have already seen column matching in Lesson F.
Example of Row matching
| 2 | In cell C1, write "Radius" |
| 3 | Type some numbers into cells C2:C11. |
| 5 | Use Excel's Formulas | Defined Names | Create from selection command with the "Top row" command selected |
| 6 | Press F5, the shortcut for Goto, and satisfy yourself that a name "Radius" has been created. Dismiss the Goto dialog when you have done so. |
| 7 | In cell E1, write "Area" |
| 8 | In cell E2, write =Pi()*Radius^2 |
| 9 | Copy cell E2 into cells E3:E11. |
| 10 | See that Excel is using row matching to pick out the radius of each circle listed to calculate its area. |
Example of Row and Column matching simultanEously
| 2 | Press Ctrl+A. This selects all the cells on Sheet1. |
| 3 | Using Excel's Formulas | Defined names | Define name command, define a name "ValueOnSheet1" so that it refers to all the cells just selected. |
| 4 | Put some random entries in cells in the first few rows on Sheet1. |
| 5 | Move to Sheet2. Type =ValueOnSheet1 into a cell and copy it into the first few rows of that sheet. |
| 6 | See that where there you have put entries in Sheet1, they appear in corresponding cells on Sheet2. To do this Excel is matching both the rows and the columns in order to reduce a two dimensional range of cells to a single value that it can usefully place in a cell. |
See also
Names tutorial J
|