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

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.

You can use the following sample formula to find the value of the last valid cell in a row where <StartCell> is the address of the first cell of a range, and <Range> 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 where <StartCell> is the address of the first cell of a range, and <Range> 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.

1. 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:
   					

2. In cell E1, type the following formula:
   =OFFSET(A1,0,MATCH(MAX(A1:D1)+1,A1:D1,1)-1)
3. With cell E1 selected, grab the fill handle and fill the formula down through cell E4.
4. In cell A5, type the following formula:
   =OFFSET(A1,MATCH(MAX(A1:A4)+1,A1:A4,1)-1,0)
5. With cell A5 selected, grab the fill handle and fill right the formula through cell D5.
6. The resulting worksheet looks similar to the following:

      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:
   					

Example 2

This example uses a checkbook to demonstrate how to always display the current balance of the checkbook.

1. Type the following information in a worksheet:

      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:
   					

2. Type the following formula in cell D3:
   =B3
3. Type the following formula in cell D4:
   =D3+B4
4. 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:
5. 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.
6. Type the following information in the worksheet:
   A10: 2/1/96��B10: -125��C10: Payment
7. 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.

For more information about OFFSET worksheet function, click Microsoft Excel Help on the Help menu, type offset worksheet function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about MATCH worksheet function, click Microsoft Excel Help on the Help menu, type match worksheet function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about MAX worksheet function, click Microsoft Excel Help on the Help menu, type max worksheet function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.