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


The information in this article applies to:

  • Microsoft Excel 2000
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(<StartCell>,0,MATCH(MAX(Range)+1,<Range>,1)-1)

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

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

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.

REFERENCES

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.
Modification Type:MajorLast Reviewed:9/27/2003
Keywords:kbhowto KB213917
©2003 Microsoft Corporation. All rights reserved.