# Locating text in a column and returning the text found

#### glchat

##### New Member
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!

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### Trouttrap2

##### Well-known Member
Try:
=LOOKUP("zzzzzzzzz",IF(ISTEXT(AO9:AO999),AO9:AO999))

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

#### glchat

##### New Member
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!

##### MrExcel MVP
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)

Replies
7
Views
1K
Replies
7
Views
568
Replies
1
Views
269
Replies
2
Views
482
Replies
0
Views
547

1,170,931
Messages
5,872,772
Members
432,944
Latest member
mj02

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back