How to Return the Rightmost Number from a Range?
This article explains how to retrieve the rightmost numeric value from a specified range in Excel. If you need to extract the last numeric value in a row or range, several methods are available.
Method 1: Using the INDEX and MATCH Functions
The combination of the INDEX and MATCH functions is an effective way to find the rightmost numeric value in a range. This approach uses the MATCH function to identify the column number of the last numeric value and the INDEX function to return the corresponding value.
How to Use INDEX and MATCH
The MATCH function identifies the position of the last numeric value in a range. Then, the INDEX function retrieves the value based on the row and column numbers.
The syntax is as follows:
=INDEX(range, row_number, column_number)
To find the rightmost column number containing a numeric value, use:
=MATCH(1E+30, range)
Here, 1E+30 represents a very large number, ensuring the function searches for the largest number in the range. The MATCH function identifies the last numeric value that is less than or equal to 1E+30, effectively finding the rightmost numeric value.
Example: Retrieve the Rightmost Numeric Value
Consider the following table where cells A1:D1 contain numeric values:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 10 | 20 | 30 | 40 |
To find the rightmost numeric value in the range A1:D1, use the following formula:
=INDEX(A1:D1, 1, MATCH(1E+30, A1:D1))
This formula returns “40,” the value in cell D1, which is the rightmost numeric value in the range.
Notes:
- The row and column numbers refer to the position within the specified range, not the absolute position in the worksheet.
- If the range is B2:D2, B2 is position (1,1) relative to the range, not (2,2).
Method 2: Using the LOOKUP Function
The LOOKUP function is another way to find the rightmost numeric value in a range. This method searches for the largest number in the range and returns the corresponding value.
How to Use the LOOKUP Function
The LOOKUP function searches for a specified value within a range and returns the corresponding result. When using 1E+30 as the lookup value, the function finds the rightmost numeric value in the range.
The syntax is as follows:
=LOOKUP(lookup_value, range)
Example: Using LOOKUP to Retrieve the Rightmost Numeric Value
Using the same table as above, to find the rightmost numeric value in the range A1:D1, use the following formula:
=LOOKUP(1E+30, A1:D1)
This formula returns “40,” the value in cell D1.
Summary
To retrieve the rightmost numeric value from a range, you can use either the INDEX and MATCH functions or the LOOKUP function. The combination of INDEX and MATCH offers greater flexibility for complex ranges, while LOOKUP provides a simpler solution for sorted ranges.