Search code examples
excelexcel-formula

First the nth value in a table with a specific condition in Excel

I have a table with some blank and non-blank cells. Like this: enter image description here For this case, I want to find the nth cell that fulfilled the condition, which is having a blank cell directly on top of it. In the case above, the cells are counted from left to right, bottom to top.

The data and position would be changed in the table, so I'll be appreciated if there is a formula that can return the numbered value in ascending order.

Solution

  • =LET(a,A1:G12,s,SORTBY(a,-ROW(a)),b,DROP(s,-1),TOCOL(IFS(LEN(b)*(LEN(DROP(s,1))=0),b),2))

    a is your full range input. s is the range input sorted from last row to first. b is the sorted range without the last row. Then the logic is: IFS(LEN(x)*(LEN(y)=0),b) Which only returns the value of the sorted range without the last row if it's length is not 0 and if it's length of the cell below (above in unsorted range) is 0.

    Originally posted logic was shortened with help of Rotabor in the comments. Original: =LET(a,A1:F15,s,SORTBY(a,-ROW(a)),b,DROP(s,-1),x,LEN(b),y,LEN(DROP(s,1)),TOCOL(IFS((x>0)*(x-y=x),b),2))