Is there a function for: If a column contains certain text, return the value
of the corresponding row from another column?

Column A has names, Column G has "x" that I used to mark the row where I
would like the names referenced on a different sheet? For example

Column A Column B
Ann X
Bob
Carol X
Doug X
Eric
Fred X
Greg

I only want a list that shows:
Ann
Carol
Doug
Fred

If the "x" was removed from their row, their name will not appear on the
list. Thank you so much!!

Assume source data as posted is in sheet named: z
Then in your other sheet,
In A2: =IF(z!G2="X",ROW(),"")
In B2: =IF(ROWS($1:1)COUNT(A:A),"",INDEX(z!A:A,SMALL(A:A ,ROWS($1:1))))
Copy A2:B2 down to cover the max expected extent of source data, say to B20?
Minimize/hide col A. Col B will return the desired results, all neatly packed
at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Daly" wrote:
Is there a function for: If a column contains certain text, return the value
of the corresponding row from another column?

Column A has names, Column G has "x" that I used to mark the row where I
would like the names referenced on a different sheet? For example

Column A Column B
Ann X
Bob
Carol X
Doug X
Eric
Fred X
Greg

I only want a list that shows:
Ann
Carol
Doug
Fred

If the "x" was removed from their row, their name will not appear on the
list. Thank you so much!!

Try this....

Assume your date is on Sheet1 in the range A2:B8.

Let's create some defined named ranges...

Goto the menu InsertNameDefine
Name: Names
Refers to: =Sheet1!$A$2:$A$8

Name: Status
Refers to: =Sheet1!$B$2:$B$8

OK out

On Sheet2 in cell A1 enter this formula. This will return the count of
records:

=COUNTIF(Status,"x")

Enter this array formula** in A2 to extract the names from Sheet1:

=IF(ROWS(A$2:A2)A$1,"",INDEX(Names,SMALL(IF(Statu s="x",ROW(Names)),ROWS(A$2:A2))-MIN(ROW(Names))+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.

Copy the formula in A2 down to enough cells that will ensure all the names
are returned. How many cells that is only you can know! If it's possible
that every name can have a "x" then you have to copy the formula to a number
of cells that is at least equal to the total number of names in your list.

--
Biff
Microsoft Excel MVP


"Daly" wrote in message
...
Is there a function for: If a column contains certain text, return the
value
of the corresponding row from another column?

Column A has names, Column G has "x" that I used to mark the row where I
would like the names referenced on a different sheet? For example

Column A Column B
Ann X
Bob
Carol X
Doug X
Eric
Fred X
Greg

I only want a list that shows:
Ann
Carol
Doug
Fred

If the "x" was removed from their row, their name will not appear on the
list. Thank you so much!!

XL-2007:
=IFERROR(INDEX(rngA,SMALL(IF(rngB="X",ROW(INDIRECT ("1:"&ROWS(rngA)))),ROWS($1:1))),"")

ctrl+shift+enter, not just enter
copy down


All versions:
=IF(COUNTIF(rngB,"X")=ROWS($1:2),INDEX(rngA,SMALL (IF(rngB="X",ROW(INDIRECT("1:"&ROWS(rngA)))),ROWS( $1:2))),"")

ctrl+shift+enter, not just enter
copy down


"Daly" wrote:

Is there a function for: If a column contains certain text, return the value
of the corresponding row from another column?

Column A has names, Column G has "x" that I used to mark the row where I
would like the names referenced on a different sheet? For example

Column A Column B
Ann X
Bob
Carol X
Doug X
Eric
Fred X
Greg

I only want a list that shows:
Ann
Carol
Doug
Fred

If the "x" was removed from their row, their name will not appear on the
list. Thank you so much!!

Correction on the last formula:

Should be: ROWS($1:1) instead of ROWS($1:2)

"Teethless mama" wrote:

XL-2007:
=IFERROR(INDEX(rngA,SMALL(IF(rngB="X",ROW(INDIRECT ("1:"&ROWS(rngA)))),ROWS($1:1))),"")

ctrl+shift+enter, not just enter
copy down


All versions:
=IF(COUNTIF(rngB,"X")=ROWS($1:2),INDEX(rngA,SMALL (IF(rngB="X",ROW(INDIRECT("1:"&ROWS(rngA)))),ROWS( $1:2))),"")

ctrl+shift+enter, not just enter
copy down


"Daly" wrote:

Is there a function for: If a column contains certain text, return the value
of the corresponding row from another column?

Column A has names, Column G has "x" that I used to mark the row where I
would like the names referenced on a different sheet? For example

Column A Column B
Ann X
Bob
Carol X
Doug X
Eric
Fred X
Greg

I only want a list that shows:
Ann
Carol
Doug
Fred

If the "x" was removed from their row, their name will not appear on the
list. Thank you so much!!

On Tue, 28 Jul 2009 18:24:01 -0700, Daly
wrote:

Is there a function for: If a column contains certain text, return the value
of the corresponding row from another column?

Column A has names, Column G has "x" that I used to mark the row where I
would like the names referenced on a different sheet? For example

Column A Column B
Ann X
Bob
Carol X
Doug X
Eric
Fred X
Greg

I only want a list that shows:
Ann
Carol
Doug
Fred

If the "x" was removed from their row, their name will not appear on the
list. Thank you so much!!

If your data is on Sheet2, then

=IF(COUNTIF(Sheet2!$B$1:$B$100,"X")<ROWS($1:1),"",
INDEX(Sheet2!$A$1:$A$100,LARGE(ROW($B$1:$B$100)*
(Sheet2!$B$1:$B$100="X"),ROWS($1:1))))

This formula must be **array-entered**

Make sure all your ranges are the same size.
Fill down as far as required.

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.
--ron

On Tue, 28 Jul 2009 18:24:01 -0700, Daly
wrote:

Is there a function for: If a column contains certain text, return the value
of the corresponding row from another column?

Column A has names, Column G has "x" that I used to mark the row where I
would like the names referenced on a different sheet? For example

Column A Column B
Ann X
Bob
Carol X
Doug X
Eric
Fred X
Greg

I only want a list that shows:
Ann
Carol
Doug
Fred

If the "x" was removed from their row, their name will not appear on the
list. Thank you so much!!

By the way, my previous solution returns the names in the reverse order of
their entry into your original table. To have them come out in the same order,
try this formula, also **array-entered**:

INDEX(Sheet2!$A$1:$A$100,LARGE(ROW($B$1:$B$100)*
(Sheet2!$B$1:$B$100="X"),COUNTIF(
Sheet2!$B$1:$B$100,"X")+1-ROWS($1:1))))
--ron

Hi,

Here is a way to do this without an array formula:

=IF(ROW(A1)<=COUNTA(Sheet1!B$1:B$9),INDEX(Sheet1!A $1:A$9,SUMPRODUCT(LARGE((Sheet1!B$1:B$9="X")*ROW(B $1:B$9),ROW(A1)))),"")

This will put them in reverse order but that may not be critical. You just
copy this formula down a number of rows equal to all of your data or further
if you wish. This assumes the first entry is on row 1.

A shorter but slower version of the formula would be:

=IF(ROW(A1)<=COUNTA(Sheet1!B:B),INDEX(Sheet1!A:A,S UMPRODUCT(LARGE((Sheet1!B:B="X")*ROW(B:B),ROW(A1)) )),"")

This second formula will only work in 2007 because it references the entire
columns.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Daly" wrote:

Is there a function for: If a column contains certain text, return the value
of the corresponding row from another column?

Column A has names, Column G has "x" that I used to mark the row where I
would like the names referenced on a different sheet? For example

Column A Column B
Ann X
Bob
Carol X
Doug X
Eric
Fred X
Greg

I only want a list that shows:
Ann
Carol
Doug
Fred

If the "x" was removed from their row, their name will not appear on the
list. Thank you so much!!