Locating text in a column and returning the text found

glchat

New Member
Joined
Jun 11, 2012
Messages
3
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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Trouttrap2

Well-known Member
Joined
May 11, 2010
Messages
612
Try:
=LOOKUP("zzzzzzzzz",IF(ISTEXT(AO9:AO999),AO9:AO999))

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

glchat

New Member
Joined
Jun 11, 2012
Messages
3
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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)
 

Forum statistics

Threads
1,137,294
Messages
5,680,660
Members
419,923
Latest member
Kalthus

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top