IF column contains certain text return value of corresponding row
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
|