Pulling data from the first non-blank cell

[Memi876]

Hi all,

I've been trying to return a date value from the first non blank cell in a row, but for some reason the formula keeps returning the value from the last cell in the range rather than the first non-blank cell. I've been using the following formula:

=INDEX(range,MATCH(FALSE,ISBLANK(range),0))

Any ideas or suggestions on how to solve this would be greatly appreciated!

 

[lrobbo314]

Book1 (version 1).xlsb
	B	C	D
20			5/9/2022
21
22
23
24	5/9/2022
25
26	5/10/2022
27
28	5/11/2022
29
Sheet1

Cell Formulas
Range	Formula
D20	D20	=INDEX(B20:B29,MATCH(1,1/(B20:B29<>""),0))

 

[Memi876]

Book1 (version 1).xlsb
	B	C	D
20			5/9/2022
21
22
23
24	5/9/2022
25
26	5/10/2022
27
28	5/11/2022
29
Sheet1

Cell Formulas
Range	Formula
D20	D20	=INDEX(B20:B29,MATCH(1,1/(B20:B29<>""),0))

Thanks for the suggestion! Unfortunately this gives me a VALUE error, I've tried it a few times.

 

[Peter_SSs]

Are the values in the range produced by formulas, so the 'blanks' actually contain a zero-length string ""?

If so, you could try this (Might need Ctrl+Shift+Enter confirmation in your version)

=INDEX(range,MATCH(TRUE,LEN(range)>0,0))