Pull First Text Value in a Column

[CrushFabrication]

Hello all -

I hope everyone is doing well and thank you to everyone who contributes to this fantastic community. I have been saved by you all's wisdom and expertise many times over the years, but I finally have a need where I can't find a solution already defined on the forum.

I have a table (T1) of data that is populated by pulling data from an accompanying raw data table (T2) via a series of IF statements. Due to this, every cell in T1 technically has a value even though many cells are displaying as blank because the IF statements are returning "". I need a formula that will scan the column and return the first non "" value in the column (but still be able to pull 0 values). I've found numerous solutions that look to pull the first non-blank value, but unfortunately it's returning "" because every cell in the column is technically not blank.

I hope this is making sense, but happy to provide more information if it doesn't.

Here are some of the solutions I've found that are close, but still pull "" values first rather than the first cell actually displaying text. Any ideas?

=XLOOKUP("*",C5:C16,C5:C16,,2)

=INDEX(C5:C16,MATCH("*",C5:C16,0))

=INDEX(C5:C16,1,MATCH(1,INDEX(1-ISBLANK(C5:C16),1,0),0)

 

[Tetra201]

... return the first non "" value in the column...

Try

=XLOOKUP(TRUE,C5:C16<>"",C5:C16,,0)

 

[CrushFabrication]

Worked like a charm. Thanks so much, Tetra201!

 

[CrushFabrication]

This might not be possible, but is there a way to modify the formula to pull the second, or third, or fourth, etc. text value in the row rather than the first?

 

[Tetra201]

Here is a formula that returns the 2nd non-"" value:
=INDEX(C5:C16,SMALL(IF(C5:C16<>"",ROW(C5:C16)-ROW(C5)+1),2))

 

[Cubist]

Another option. Change the 2 to get the nth non-empty string value.

=INDEX(FILTER(C5:C16,C5:C16<>""),2)

 

[CrushFabrication]

Both of these solutions worked. Thank you both!