After posting some Excel vs. Google Sheets formula comparisons, I am back with a specific Google Doc Sheets tutorial. Do you ever want to find the row numbers of change in values in Google Sheets? Then find the answer below.
My formula would work only in a sorted range. The reason is the use of Lookup function which requires a sorted range.
Change in Values in a Column Range in Google Sheets
Change in values can be interpreted in two ways.
You can consider either the last value in a group as a change in value or the very next value. The below screen capture can hopefully make this clearer to you.
The value “Apple” changes at row # 4. So normally you can consider row # 4 as the change in value row. But some of you may want to consider the row # 3 as the change in value since it’s the last row that contains the value “Apple”.
I have demonstrated the same in column C and D in the above example. Let’s see how to find the row numbers of change in values as above in Google Sheets.
Lookup the Row Numbers of Change in Values in Google Sheets
I am using the function Lookup together with the Row function to find the row numbers of the last values in each group.
Formula # 1 in Cell C2:
=iferror(ArrayFormula(lookup(unique(A2:A),A2:A,row(A2:A)+1)))
Formula # 2 in Cell D2:
=iferror(ArrayFormula(lookup(unique(A2:A),A2:A,row(A2:A))))
Row Numbers of Change in Value: Formula Explanation
As I have mentioned, the core formula is Lookup here. The Lookup will work ‘only’ in a sorted range. But you can tweak that to work in an unsorted range too – How to Use LOOKUP Function in an Unsorted Array in Google Sheets. But that is not relevant here.
What important here is the Lookup syntax in use! Yes, you can use Lookup in two ways. You may check my Lookup tutorial to understand that.
Must Check: Google Sheets Functions Guide.
Here I am following the below syntax in my both the above formulas.
Syntax:
LOOKUP(search_key, search_range, result_range)
search_key: My search_key as per the above example is “Apple”, and “Banana”. I have used the Unique function to return that.
=unique(A2:A)
search_range: The search range of the above search_keys are of course the range A2: A.
The Lookup will search down the search_keys in the range A2: A and find the relative position of the last matching values and return the corresponding values from result_range.
result_range: The result_range is the row function as below.
In formula 1;
=row(A2:A)+1
This formula returns the row numbers in sequential like 3, 4, 5 … when using it together with the ArrayFormula function.
In formula 2;
=row(A2:A)
This formula can return the row numbers in sequential like 2, 3, 4… with ArrayFormula function.
I have used the Lookup as it can find the last value in each group. So it’s easy to return the row numbers as the result_range as above.
This way you can quickly Lookup the row numbers of change in values in Google Sheets.
