Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Finding Text within an Array

Hi all,

I'm a bit of a MS novice so I apologize if the following question is mundane.

I have an array (A1:A25) and I have written a formula in each cell that ensures only one cell will populate with text. All other cells that do not match the criteria will populate as blank cells.

My question is, how would I go about searching for the cell that contains the text and then returning the text from the cell? The text is dependent on other cells, so I cannot just set one cell equal to another.

Please let me know if I need to elaborate more.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 153
Default Finding Text within an Array

I have an array (A1:A25) and I have written a formula in each cell that
ensures only one cell will populate with text. All other cells that do not
match the criteria will populate as blank cells.

My question is, how would I go about searching for the cell that contains the
text and then returning the text from the cell?


You could concatenate all the cells. Since only one is non-blank, that returns its text.
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,045
Default Finding Text within an Array

On Thu, 30 May 2013 08:15:02 -0700 (PDT), Stephan Grunwald wrote:

Hi all,

I'm a bit of a MS novice so I apologize if the following question is mundane.

I have an array (A1:A25) and I have written a formula in each cell that ensures only one cell will populate with text. All other cells that do not match the criteria will populate as blank cells.

My question is, how would I go about searching for the cell that contains the text and then returning the text from the cell? The text is dependent on other cells, so I cannot just set one cell equal to another.

Please let me know if I need to elaborate more.


In addition to the concatenation idea advanced by zvkmpw, you could also use this formula:

=IFERROR(LOOKUP(2,1/(LEN(A1:A25)0),A1:A25),"")

It actually returns the last non-blank cell in the array, but since you only have one, that would be the same cell.
The IFERROR is in case all cells are blank, in which case the LOOKUP function would return #NA
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Finding Text within an Array

Hi all,



I'm a bit of a MS novice so I apologize if the following question is mundane.




I have an array (A1:A25) and I have written a formula in each cell that ensures only one cell will populate with text. All other cells that do not match the criteria will populate as blank cells.




My question is, how would I go about searching for the cell that contains the text and then returning the text from the cell? The text is dependent on other cells, so I cannot just set one cell equal to another.




Please let me know if I need to elaborate more.




In addition to the concatenation idea advanced by zvkmpw, you could also use this formula:



=IFERROR(LOOKUP(2,1/(LEN(A1:A25)0),A1:A25),"")



It actually returns the last non-blank cell in the array, but since you only have one, that would be the same cell.

The IFERROR is in case all cells are blank, in which case the LOOKUP function would return #NA


The iferror(lookup(... worked beautifully. Thank you very much!
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,045
Default Finding Text within an Array

On Fri, 31 May 2013 05:35:14 -0700 (PDT), Stephan Grunwald wrote:

The iferror(lookup(... worked beautifully. Thank you very much!


Glad to help. Thanks for the feedback.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Finding a value or the one next to it in a range NOT array advanced novice Excel Worksheet Functions 3 August 6th 08 02:54 PM
Help in finding Value in Index Array George Excel Discussion (Misc queries) 20 October 30th 07 12:48 AM
Finding the least frequent value in an array Dave D[_2_] Excel Discussion (Misc queries) 3 May 5th 07 12:56 PM
Finding Data in an Array Hannah Excel Worksheet Functions 5 April 17th 07 05:10 AM
Finding Array Formulas in VBA ExcelMonkey Excel Programming 7 July 19th 05 11:09 PM


All times are GMT +1. The time now is 03:24 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"