XL: Using OFFSET, MATCH, and MAX to Return Last Value in Range
ID: Q152407
|
The information in this article applies to:
-
Microsoft Excel 97 for Windows
-
Microsoft Excel for Windows 95, versions 7.0, 7.0a
-
Microsoft Excel for Windows, versions 5.0, 5.0c
-
Microsoft Excel for Windows NT, version 5.0
-
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
-
Microsoft Excel 98 Macintosh Edition
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.
- 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:
- In cell E1, type the following formula:
E1: =OFFSET(A1,0,MATCH(MAX(A1:D1)+1,A1:D1,1)-1)
- With cell E1 selected, grab the fill handle and fill the formula down
through cell E4.
- In cell A5, type the following formula:
A5: =OFFSET(A1,MATCH(MAX(A1:A4)+1,A1:A4,1)-1,0)
- With cell A5 selected, grab the fill handle and fill right the formula
through cell D5.
- 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.
- 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:
- Type the following formulas into the worksheet:
D3: =B3
D4: =D3+B4
- 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:
- 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.
- 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:
Q139574 Returning the Address of the Cell with Largest or Smallest Value
Q142526 How to Determine Top and Bottom Used Cells in a Sparse Array
Q85234 Excel: Formula To Find Last Valid Cell in a Range
REFERENCESMicrosoft 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
Additional query words:
98 8.00 XL97
Keywords : xlformula
Version : MACINTOSH:5.0,5.0a,98; WINDOWS:5.0,5.0c,7.0,7.0a,97; winnt:5.0
Platform : MACINTOSH WINDOWS winnt
Issue type : kbhowto
|