I have a google form which is returning data into Column A and Column B and I need to perform calculations on that in Column C, D & E. I have been using Array Formula so that C & D auto-calculate whenever a new entry comes in.
However, in Column E, I want to check if the latest entry in Column B (a unique id) is the latest entry of that unique id or not. If the unique id has been repeated twice, I will ignore the previous entry for all calculations in a different sheet.
In order to achieve this, Column E is titled Latest If the value in Column B has appeared before, Latest (Column E) will be 1 for the latest entry and 0 for the remaining entries.
Here, I have used the formula:
In this formula, for B3, I want a match to search from B4 onwards to the bottom (all entries after B3). However, because it is an array formula where B3 is referenced as $B$3:$B, it takes $B4:$B as an absolute reference instead of a relative reference. B3 needs to search from B4 downwards, B4 needs to search from B5 downwards.
How do I achieve a relative changing reference for Match like this in an Array Formula?