Looking up the 2nd, 3rd, 4th, non black cell

Occasional Contributor


Im hoping to get some help I need to look up the 2, 3, 4, 5, etc cell that is not blank.

This is the current formula I have that gives me the first un-blank cell, how do I update this to locate the 2 un-blank cell

=INDEX('Form Responses 1'!L2:AU2,match(true,INDEX('Form Responses 1'!L2:AU2<>"",1,0),0))

1 Reply


=INDEX('Form Responses 1'!$L$2:$AU$2,SMALL(IF('Form Responses 1'!$L$2:$AU$2<>"",COLUMN('Form Responses 1'!$L$2:$AU$2)-11),COLUMN(A$1)))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

Looking up the 2nd, 3rd, 4th, non black cell.JPG