If cell in range contains ANY text, return value of that cell

[ajoshi87]

Hello everyone,

I have a range of cells A2:A15 and I would like to write a formula that says, if there is a cell from A2:A15 that has ANY text, return the value of that cell.

As a caveat, that range of will never have 2 cells that have text.

Any help writing this formula would be appreciated

 

[StephenPottage]

This is awesome. Exactly what I was looking for. Quick question, how can I turn this into an array to automatically apply it to every row. I've tried with a standard arrayformula but it isn't outputting anything.

=IFERROR(INDEX(L22:U22,MATCH($X$1&"*",L22:U22,0)),"") - this formula works perfectly if dragged down.

=ArrayFormula(IFERROR(INDEX(L2:U,MATCH($X$1&"*",L2:U,0)),"")) - produces no result.

 

[T. Valko]

This is awesome. Exactly what I was looking for. Quick question, how can I turn this into an array to automatically apply it to every row. I've tried with a standard arrayformula but it isn't outputting anything.

=IFERROR(INDEX(L22:U22,MATCH($X$1&"*",L22:U22,0)),"")- this formula works perfectly if dragged down.

=ArrayFormula(IFERROR(INDEX(L2:U,MATCH($X$1&"*",L2:U,0)),"")) - produces no result.

Sorry, I have no experience with Google spreadsheets.