Find Text

[SLHayden]

I need a formula that will find which cell in a column has text in it, and then return what ever is in that cell.

For Example: The selection of cells A1:A20, if cell A15 has "DOG" in it (all other will be blank) A22 will will return "DOG".

The text in the cell will not be a specific word.

 

[Jonmo1]

So only 1 cell in the range will contain a text value, all others will be blank?

Try

=LOOKUP(REPT("z",255),A1:A20)

It will error if ALL cells are blank.


Hope that helps.

 

[SLHayden]

I think I am doing something wrong. I pasted the formula into the spreadsheet, worked like a charm. Once I replaced the lookup vector with the actual cells I need there is nothing returned (not "0", "False") just a blank cell. There is a populated cell in the selection.

 

[Jonmo1]

Are the blanks in your range truely blank, or are they formulas that return "" ?

 

[SLHayden]

The cell that are selected contain the formula =IF(BC18="WINS",A18,"")

 

[Jonmo1]

Try

=LOOKUP(2,1/(A1:A20<>""),A1:A20)

 

[SLHayden]

jonmo1 thanks I got it to work I just remove the "" and let the formula return "0" and it worked out.

 

[Aladin Akyurek]

jonmo1 thanks I got it to work I just remove the "" and let the formula return "0" and it worked out.

When there are formula blanks along with other text value(s)...

=LOOKUP(9.99999999999999E+307,SEARCH("?",A1:A20),A1:A20)

will do as well.