Formula help

[wildbio]

I need a formula that will return a value in a different column but the same row. ie. if any cell in column A contains the text "YES", return the contents (text) in the corresponding row of column C. All rows of column A will be filled and only some of column C.

 

[Scott Huish]

=IF(A1="YES",C1,"")

Copy down.

 

[VoG]

Try

=IF(ISNUMBER(FIND("YES",A1)),A1,"")

 

[Rick Rothstein]

Does this formula do what you want?

=IF(COUNTIF(A1,"*yes*"),C1,"")

 

[wildbio]

Thanks but the formula will be in one cell in a different worksheet. I need it to scan the columns like the countif function does.

 

[wildbio]

Does this formula do what you want?

=IF(COUNTIF(A1,"*yes*"),A1,"")

No, I need it to look through the entire column of data and find the "Yes"s without telling it which specific cell to look in.

 

[Rick Rothstein]

Thanks but the formula will be in one cell in a different worksheet. I need it to scan the columns like the countif function does.

Then prefix the cell references with the worksheets they are on. For my formula, something like this where you would replace my 'Sheet 1' reference with your actual sheet name...

=IF(COUNTIF('Sheet 1'!A1,"*yes*"),'Sheet 1'!C1,"")

 

[wildbio]

Then prefix the cell references with the worksheets they are on. For my formula, something like this where you would replace my 'Sheet 1' reference with your actual sheet name...

=IF(COUNTIF('Sheet 1'!A1,"*yes*"),'Sheet 1'!C1,"")

Thanks but again, that only looks in one cell, I need it to check all cells in the column.

 

[Rick Rothstein]

Thanks but again, that only looks in one cell, I need it to check all cells in the column.

None of what you actually wanted has been deducible until your message at Message #6. Give this formula a try...

=INDEX('Sheet 1'!C:C,MATCH("*yes*",'Sheet 1'!A:A,0))

The above formula will find cells with "yes" in them even if the "yes" is embedded in other text. If the "yes" you are looking for is to be the only text in the cell, then remove both asterisks so that you won't get an accidental match with an early cell contain text that includes an embedded "yes" in it.

 

[wildbio]

None of what you actually wanted has been deducible until your message at Message #6. Give this formula a try...

=INDEX('Sheet 1'!C:C,MATCH("*yes*",'Sheet 1'!A:A,0))

The above formula will find cells with "yes" in them even if the "yes" is embedded in other text. If the "yes" you are looking for is to be the only text in the cell, then remove both asterisks so that you won't get an accidental match with an early cell contain text that includes an embedded "yes" in it.

That's not working. It gives me an #N/A. Let me try to explain what I need more clearly. I need a formula to scan two columns that looks something like this
No X
Yes X
Yes
No X
Yes X
I need it to return the number 2 because there are two Yes's in the first column that have corresponding Xs in the second column.