Microsoft KB Archive/213917

= XL2000: Using the OFFSET, MATCH, and MAX Worksheet Functions to Return the Last Value in a Range =

Article ID: 213917

Article Last Modified on 9/27/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q213917





SUMMARY
In Microsoft Excel, you can use a combination of the OFFSET, MAX, and MATCH worksheet functions to return the value of the last valid cell in a range of cells.



MORE INFORMATION
You can use the following sample formula to find the value of the last valid cell in a row

=OFFSET(,0,MATCH(MAX(Range)+1,,1)-1)

where  is the address of the first cell of a range, and  is the address of the cells containing the data.

You can use the following sample formula to find the value of the last valid cell in a column

=OFFSET(,MATCH(MAX()+1,,1)-1,0)

where  is the address of the first cell of a range, and  is the address of the cells containing the data.

To use these formulas, use the steps in the following examples.

Example 1
This example demonstrates using both formulas to find the value of the last valid cell in both a row and a column.   Start Excel and enter the following data in a new worksheet:   A1:  1     B1:        C1:  2      D1:  1      E1: A2:       B2:  2     C2:  14     D2:         E2: A3: 9     B3:  4     C3:         D3:  10     E3: A4:       B4:        C4:  5      D4:         E4: A5:       B5:        C5:         D5:         E5: </li> <li>In cell E1, type the following formula:

=OFFSET(A1,0,MATCH(MAX(A1:D1)+1,A1:D1,1)-1)

</li> <li>With cell E1 selected, grab the fill handle and fill the formula down through cell E4.</li> <li>In cell A5, type the following formula:

=OFFSET(A1,MATCH(MAX(A1:A4)+1,A1:A4,1)-1,0)

</li> <li>With cell A5 selected, grab the fill handle and fill right the formula through cell D5.</li> <li> The resulting worksheet looks similar to the following: <pre class="fixed_text">  A1:  1     B1:        C1:  2      D1:  1      E1:  1 A2:       B2:  2     C2:  14     D2:         E2:  14 A3: 9     B3:  4     C3:         D3:  10     E3:  10 A4:       B4:        C4:  5      D4:         E4:  5 A5: 9     B5:  4     C5:  5      D5:  10     E5: </li></ol>

Example 2
This example uses a checkbook to demonstrate how to always display the current balance of the checkbook. <ol> <li> Type the following information in a worksheet: <pre class="fixed_text">  A1:          B1:                C1:  Current Balance  D1: A2: Date    B2: Transaction    C2:  Description      D2: Balance A3: 1/1/00  B3: 125            C3:  Opening Balance  D3: A4:         B4:                C4:                   D4: A5: 1/5/00  B5: 100            C5:  Deposit          D5: A6: 1/6/00  B6: -115           C6:  Payment          D6: A7: 1/7/00  B7: 65             C7:  Deposit          D7: A8:         B8:                C8:                   D8: A9:         B9:                C9:                   D9: A10:        B10:               C10:                  D10: </li> <li>Type the following formula in cell D3:

=B3

</li> <li>Type the following formula in cell D4:

=D3+B4

</li> <li>With cell D4 selected, grab the fill handle and fill down the formula through cell D7.

After you fill the formula down, the results are similar to the following:

D1:

D2: Balance

D3: 125

D4: 125

D5: 225

D6: 110

D7: 175

D8:

D9:

D10:

</li> <li>In cell D1, type the following formula to display the current balance:

=OFFSET(A2,MATCH(MAX(A3:A10),A3:A10,0),3)

This formula returns the current balance of 175 in cell D1, which is the same value as the value in cell D7.</li> <li>Type the following information in the worksheet:

A10: 2/1/96  B10: -125  C10: Payment

</li> <li>With cell D7 selected, grab the fill handle and fill the formula down through cell D10.

After filling the formula down, the results are similar to the following:

D1: 50 D2: Balance D3: 125 D4: 125 D5: 225 D6: 110 D7: 175 D8: 175 D9: 175 D10: 50

Note that the Current Balance in cell D1 now reflects the value in cell D10.</li></ol>

<div class="references_section">