Conditional Testing of an array
 

Ok, my spreadsheet skills may be showing a little out of date here, but I am
hoping an Excel-God can show me the light.

I have an array consisting of column A, column B, with 15 separate rows.
The array is periodically filled with various text strings, but the data in
column B always contains four occurences of the letter "X" in different boxes.

I think I need four formulas that will be similar - I want to find the first
occurence of "X" in column B and return its matching text label in Column A
as a result. In the next box, I want to find the second occurence of "X" and
return its label from Column A, and so on for the 3rd and 4th occurence.

In my youth, this was a simple matter of nesting IF statements, but with a 7
layer limitation and 15 rows to process, this isn't feasible. I'm probably
missing some learning on some better way to handle and test an array - so
clue me in if I'm missing a few brain cells on this one (cells - get it?)

Thanks!

Conditional Testing of an array
 

column B always contains four occurences of the letter "X"

Assume the data is in the range A1:B15.

Enter this array formula** in D1 and copy down to D4:

=INDEX(A$1:A$15,SMALL(IF(B$1:B$15="x",ROW(A$1:A$15 )),ROWS(D$1:D1))-MIN(ROW(A$1:A$15))+1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

For a less complicated approach...

Use a column that holds a helper formula to mark the rows that have the "x".

Entered in C1 and copied down to C15:

=IF(B1="x",ROW(),"")

Then, normally entered in D1 and copied down to D4:

=INDEX(A$1:A$15,MATCH(SMALL(C$1:C$15,ROWS(D$1:D1)) ,C$1:C$15,0))

--
Biff
Microsoft Excel MVP


"Lord Robocop" wrote in message
...
Ok, my spreadsheet skills may be showing a little out of date here, but I
am
hoping an Excel-God can show me the light.

I have an array consisting of column A, column B, with 15 separate rows.
The array is periodically filled with various text strings, but the data
in
column B always contains four occurences of the letter "X" in different
boxes.

I think I need four formulas that will be similar - I want to find the
first
occurence of "X" in column B and return its matching text label in Column
A
as a result. In the next box, I want to find the second occurence of "X"
and
return its label from Column A, and so on for the 3rd and 4th occurence.

In my youth, this was a simple matter of nesting IF statements, but with a
7
layer limitation and 15 rows to process, this isn't feasible. I'm
probably
missing some learning on some better way to handle and test an array - so
clue me in if I'm missing a few brain cells on this one (cells - get it?)

Thanks!