Using OFFSET, MATCH, and MAX to Return Last Value in Range

Article translations Article translations
Article ID: 152407 - View products that this article applies to.
This article was previously published under Q152407
Expand all | Collapse all

On This Page

SUMMARY

By using a combination of the OFFSET, MAX, and MATCH functions in a worksheet formula, you can have Microsoft Excel return the value of the last valid cell in a range of cells.

MORE INFORMATION

The following example formula will 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.

The following example formula will 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.

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. Type the following information into a 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:
          E1: =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:
          A5: =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 as follows:
          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 into a worksheet:
          A1:          B1:                C1:  Current Balance  D1:
          A2: Date     B2: Transaction    C2:  Description      D2: Balance
          A3: 1/1/96   B3: 125            C3:  Opening Balance  D3:
          A4:          B4:                C4:                   D4:
          A5: 1/5/96   B5: 100            C5:  Deposit          D5:
          A6: 1/6/96   B6: -115           C6:  Payment          D6:
          A7: 1/7/96   B7: 65             C7:  Deposit          D7:
          A8:          B8:                C8:                   D8:
          A9:          B9:                C9:                   D9:
          A10:         B10:               C10:                  D10:
  2. Type the following formulas into the worksheet:
    D3: =B3
    D4: =D3+B4
  3. With cell D4 selected, grab the fill handle and fill down the formula through cell D7. After filling the formula down, the results will be as follows:
    D1:
    D2: Balance
    D3: 125
    D4: 125
    D5: 225
    D6: 110
    D7: 175
    D8:
    D9:
    D10:
  4. In cell D1, type the following formula to display the current balance:
    D1: =OFFSET(A2,MATCH(MAX(A3:A10),A3:A10,0),3)
    This formula displays the current balance of 175 in cell D1, which is the same value as the value in cell D7.
  5. Type the following information in the following cells:
    A10: 2/1/96 B10: -125 C10: Payment D10:
    With cell D7 selected, grab the fill handle and fill down the formula through cell D10. After filling the formula down, the results will be as follows:
    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 additional information, please see the following articles in the Microsoft Knowledge Base:
139574 Returning the Address of the Cell with Largest or Smallest Value

142526 How to Determine Top and Bottom Used Cells in a Sparse Array

85234 Excel: Formula To Find Last Valid Cell in a Range

REFERENCES

Microsoft Excel 97 and Microsoft Excel 98 Macintosh Edition

For more information about the OFFSET, MATCH, and MAX functions in Microsoft Excel 97 and Microsoft Excel 98 Macintosh Edition, click Microsoft Excel Help on the Help menu and type one of the following:
Offset
Match
Max

Microsoft Excel 7.0

For more information about the OFFSET, MATCH, and MAX functions in Microsoft Excel version 7.0, click Answer Wizard on the Help menu and type:
Offset
Match
Max

Microsoft Excel 5.0

For more information about the OFFSET, MATCH, and MAX functions in Microsoft Excel version 5.0, click the Search button in Help and type:
Offset
Match
Max

Properties

Article ID: 152407 - Last Review: August 17, 2005 - Revision: 1.3
APPLIES TO
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 5.0 Standard Edition
  • Microsoft Excel 98 for Macintosh
Keywords: 
kbhowto KB152407
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com