I have a Google Sheet in the following format:

  ID |  VAL
1    | dataH
2    | dataW
3    | dataX
4    | dataC
5    | dataG
6    | dataL
...  | ...

ID and VAL are on cells A1 and B1, respectively.

In A2, I have the following formula: =ArrayFormula(if(ISBLANK(B2:B), "",ROW(A2:A)-1)), which puts in a number in the ID column if there is data in the VAL column.

However, I'd like to be able to sort data, for example from A to Z, while keeping ID constant. However, when I sort VAL, the cell containing the formula, ID 1, gets dragged down to its proper position, but then leaves all cells in the column above it blank.

I have 2 possible solutions: somehow have the formula in A1 (ID) and display ID instead of 1; or have the formula in C1 and output the results starting from A2.

Is there a way to do one of the 2 solutions above?


somehow have the formula in A1 (ID) and display ID

=ARRAYFORMULA(IF(ROW(A1:A)=1, "ID", IF(LEN(B1:B), ROW(A1:A)-1, )))

However, this will not solve your issue. The best you can do is to turn the output of array formula into static values via CTRL + C and then re-pasting it with CTRL + SHIFT + V

Also, the ID could be assigned already beforehand so when you sort it, it will act like static:

 VLOOKUP(B1:B, {SORT(B1:B), ROW(A1:A)}, 2, 0), )))


If none of the above is acceptable you will need to have formula per cell... paste in A2 cell and drag down:

=IF(LEN(B2), ROW()-1)

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.