Finding Text within an Array

S

Stephan Grunwald

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.
 
Z

zvkmpw

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.
 
R

Ron Rosenfeld

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
 
S

Stephan Grunwald

Hi all,
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top