Locating text in a column and returning the text found

[glchat]

This seems like a simple problem but I can't find any other examples on this and it is making me nuts!

Excel 2007 - I want to search cells AO9:AO999 for text...any text. There should only be one cell of text in that entire range at any given time based upon a set of other arguments in any given row. I then want that text to be returned to cell AO6.

The logic is something along the lines of this formula in cell AO6: =IF(you find any text in AO9:AO999, return that text,"")

Any help would be appreciated!

 

[Trouttrap2]

Try:
=LOOKUP("zzzzzzzzz",IF(ISTEXT(AO9:AO999),AO9:AO999))

You must CTRL+SHIFT+ENTER the formula. It isa an array formula

 

[glchat]

Try:
=LOOKUP("zzzzzzzzz",IF(ISTEXT(AO9:AO999),AO9:AO999))

You must CTRL+SHIFT+ENTER the formula. It isa an array formula

That didn't work...BUT...you got my brain pointing in the right direction!

I was able to use VLOOKUP. The text that appears in the AO column is toggled on and off by the selection of a text from a pull down menu in a preceding column. If I have an "IF" statement in the AL column that says if a certain text is selected, then "1", otherwise "0". I set-up column AL (that contains the 1 or 0) as the first column of the VLOOKUP table, then a column with the text in column 4 of the VLOOKUP table and came up with =VLOOKUP(1,AL9:AO999,4,FALSE).

I made cell AL999 always have a "1" in it and cell AO999 with a default text in it to avoid the #N/A error appearing.

Thanks for the guidance!

 

[Aladin Akyurek]

This seems like a simple problem but I can't find any other examples on this and it is making me nuts!

Excel 2007 - I want to search cells AO9:AO999 for text...any text. There should only be one cell of text in that entire range at any given time based upon a set of other arguments in any given row. I then want that text to be returned to cell AO6.

The logic is something along the lines of this formula in cell AO6: =IF(you find any text in AO9:AO999, return that text,"")

Any help would be appreciated!

That didn't work...BUT...you got my brain pointing in the right direction!

I was able to use VLOOKUP. The text that appears in the AO column is toggled on and off by the selection of a text from a pull down menu in a preceding column. If I have an "IF" statement in the AL column that says if a certain text is selected, then "1", otherwise "0". I set-up column AL (that contains the 1 or 0) as the first column of the VLOOKUP table, then a column with the text in column 4 of the VLOOKUP table and came up with =VLOOKUP(1,AL9:AO999,4,FALSE).

I made cell AL999 always have a "1" in it and cell AO999 with a default text in it to avoid the #N/A error appearing.

Thanks for the guidance!

Try...

=LOOKUP(REPT("z",255),AO9:AO999)