I have 2 columns of data. I am trying to create a formula that will search
the columns for data and return the data to the left of that column. I also
want it to list the data to the left of the column. For example, I want Excel
to look through a range of data from let's say B3:B58. I want it to find
every entry of "4th". Each time it finds "4th" in column B3:b58, I want it to
give me the name of the person listed next to the number. I am using Excel
2003

Try this...

Assume you want the names listed starting in cell D3.

Enter this array formula** in D3:

=IF(ROWS(D$3:D3)<=COUNTIF(B$3:B$58,"4th"),INDEX(A$ 3:A$58,SMALL(IF(B$3:B$58="4th",ROW(A$3:A$58)-MIN(ROW(A$3:A$58))+1),ROWS(D$3:D3))),"")

Copy down until you get blanks

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"learning" wrote in message
...
I have 2 columns of data. I am trying to create a formula that will search
the columns for data and return the data to the left of that column. I
also
want it to list the data to the left of the column. For example, I want
Excel
to look through a range of data from let's say B3:B58. I want it to find
every entry of "4th". Each time it finds "4th" in column B3:b58, I want it
to
give me the name of the person listed next to the number. I am using Excel
2003