Clever Geek Handbook

The return value of the adjacent cell

I have a piece of code that looks like this:

enter image description here

I am trying to return the cell directly below the cell that is "A", but I don't want to have spaces between the cells.

I tried to use =IF(A1="A",OFFSET(A1,1,0))

, but this adds "FALSE" between cells where it doesn't:

enter image description here

How to remove spaces with output-only formula:

enter image description here

+3


source to share


2 answers


This standard formula will collect values ​​directly in each cell that contains A.

enter image description here

Use this in B1 =IFERROR(INDEX(A:A,SMALL(INDEX(ROW(A:A)+(A:A<>"A")*1E+99,,),ROW(1:1))+1),"")

and fill in as needed.



IFERROR

returns empty strings when the return value ends. This is not the same as true blank cells.

Even though it is entered as a standard formula, this uses array processing, and if you find the calculation lagging behind the problem, compare all the column range references to something closer to what your data volume represents.

+1


source


If you'd rather avoid VBA, you can perform this calculation using the following formulas or a variation of them:

enter image description here



In the given example, the output will look like this:

Result

0


source






More articles:


All Articles