RETURN_CELL_INVERSED
Return a cell by position counting from the end of non-empty values.
=RETURN_CELL_INVERSED(range, inverseIndex)Returns: any
Overview
RETURN_CELL_INVERSED retrieves a value from a range by counting positions from the end rather than the beginning. Position 1 returns the last non-empty cell (equivalent to RETURN_LAST_CELL), position 2 returns the second-to-last, position 3 the third-to-last, and so on.
Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
range | Range | Yes | The range to search (e.g., A1:A100 or A:A). |
inverseIndex | number | Yes | Position from the end: 1 = last, 2 = second-to-last, etc. |
Examples
Get yesterday's value from a daily log
Retrieve the second-to-last value from a daily metrics column to compare with today's entry.
=RETURN_CELL_INVERSED(B2:B100, 2)Input
| 1200 |
| 1350 |
| 1400 |
| 1520 |
Output
1400Get value from 7 days ago
Retrieve the value from 7 entries ago for week-over-week comparison (position 8 = current + 7 prior).
=RETURN_CELL_INVERSED(C2:C200, 8)Input
| 100 |
| 105 |
| 98 |
| 110 |
| 115 |
| 108 |
| 120 |
| 125 |
Output
100Previous month's total
In a monthly totals column, get last month's value for comparison with the current month.
=RETURN_CELL_INVERSED(D2:D50, 2)Input
| $45,000 |
| $48,000 |
| $52,000 |
Output
$48,000Third most recent entry
Get the third-to-last non-empty value from a growing list.
=RETURN_CELL_INVERSED(A1:A20, 3)Input
| Alpha |
| Beta |
| Gamma |
| Delta |
| Epsilon |
Output
GammaUse Cases
Period-over-period comparison
Access the previous period value to calculate growth rate or change percentage against the current period.
Rolling average calculation
Retrieve the last N data points to compute rolling averages for stock prices or revenue trends.
Recent activity display
Show the last 3-5 support tickets or interactions on a customer dashboard by accessing recent positions.
Previous reading comparison
Compare a patient's current vital reading with their previous reading to detect significant changes.
Version history access
Access previous build numbers or version entries from an append-only deployment log.
Shift handover data
Display the previous shift's production count alongside the current shift for handover reporting.
Pro Tips
Use positions 1 and 2 together to calculate the change between the last two entries: =RETURN_CELL_INVERSED(B:B,1) - RETURN_CELL_INVERSED(B:B,2).
Build a "last 5 values" display by using positions 1 through 5 across five cells.
Pair with SPARKLINE to visualize recent trends from the last N data points.
Use a cell reference for the index to create a slider-like interface for browsing historical values.
This function is invaluable when you need to access recent entries in a growing dataset without knowing the exact row number. While RETURN_LAST_CELL always gets the very last entry, RETURN_CELL_INVERSED gives you flexible access to any position from the end. This is perfect for comparing recent values, calculating period-over-period changes, or displaying a rolling window of the most recent data points.
The function first collects all non-empty values from the range, then indexes from the end. Empty cells are excluded from the count, so position 1 is always the last cell that actually contains data, regardless of any trailing blank rows.
Common applications include showing the previous day's value alongside today's for comparison, calculating week-over-week changes by accessing values at positions 1 and 8, building "last N entries" displays on dashboards, and retrieving historical checkpoints from append-only logs. If the specified position exceeds the number of non-empty cells in the range, the function returns an empty string.
Common Errors
Returns empty string when data existsCause: The inverseIndex is larger than the total number of non-empty cells in the range.
Fix: Check how many non-empty cells exist using COUNTA(range) and ensure your inverseIndex does not exceed that count.
Returns unexpected valueCause: Hidden non-empty cells (containing spaces or invisible characters) are being counted as valid entries.
Fix: Clean the range by selecting trailing cells and pressing Delete. Check for cells that appear empty but contain spaces.
Error with inverseIndex of 0Cause: Zero is not a valid position. Positions start at 1.
Fix: Use inverseIndex = 1 for the last cell. The minimum valid position is 1.
Frequently Asked Questions
Position 1 returns the last non-empty cell in the range, which is identical to using RETURN_LAST_CELL. Position 2 returns the second-to-last, and so on.
No, empty cells are excluded before counting. The function only considers non-empty cells, so position 2 is always the second-to-last cell with actual data.
The function returns an empty string. For example, if there are only 5 non-empty cells and you request position 10, you get "".
Yes, subtract the previous value from the current: =RETURN_CELL_INVERSED(B:B, 1) - RETURN_CELL_INVERSED(B:B, 2) gives you the change between the last two entries.
Yes, the position should be a positive integer. Decimal values will be truncated to the integer part.
Yes, you can reference a cell containing the position number: =RETURN_CELL_INVERSED(A:A, E1). This lets you dynamically change which position from the end you are accessing.
Yes, the function flattens the range first, reading left-to-right, top-to-bottom, then counts from the end of all non-empty values.
Traditional approaches like INDEX(range, COUNTA(range) - N + 1) do not handle gaps in data correctly. RETURN_CELL_INVERSED excludes empty cells, giving you accurate positioning even with sparse data.
Related Functions
Start using RETURN_CELL_INVERSED today
Install Unlimited Sheets to get RETURN_CELL_INVERSED and 41 other powerful functions in Google Sheets.
