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?

migrated from superuser.com Jul 15 '18 at 18:04

This question came from our site for computer enthusiasts and power users.

  • can you share a copy / dummy of your sheets? – user0 Jul 15 '18 at 18:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy