SumIF will not work!

• I need a formula to return the text from the cell in one column when the cell on the same row in a different column contains a specified condition.

Exampe: If A1 contains X, then I need to display the text in cell E1.

SumIF does not work because E1 does not contain a number. Any suggestions?

• Welcome to the OzGrid forum.

The following formula should work.

=if(A1="X",E1,"")

Regards,
Barry

My Favorite New Thing:
Dynamic Named Ranges

If you get a response that answers your query or you see a response that explains something for you. Maybe think about "LIKING" that response let the member know he/she has helped another.

• I should have said that the X in column A will be random, anywhere from A1 to A200, so the formula would need to pick up the cell in column E on the same row as the one in column A. That is why I first tried SumIF.

• Are you looking for a single formula to find the "X" in column A and return the value in column E of that same row?

I

Regards,
Barry

My Favorite New Thing:
Dynamic Named Ranges

If you get a response that answers your query or you see a response that explains something for you. Maybe think about "LIKING" that response let the member know he/she has helped another.

• Hi,

edited:

=IF(ISERROR(SMALL(IF(NOT(ISERROR(SEARCH("x",\$A\$1:\$A\$200)=TRUE)),ROW(\$A\$1:\$A\$200)),ROW(A1))),"",INDIRECT("A"&SMALL(IF(NOT(ISERROR(SEARCH("x",\$A\$1:\$A\$200)=TRUE)),ROW(\$A\$1:\$A\$200)),ROW(A1))))

holding down Ctrl+Shift and press Enter to get out from the formula bar

then drag down

• Yes, iwrk4dedpr, that is exactly what I need to do.

• Well here ya go!

=IF(ISERROR(MATCH("X",A1:A200)),"",INDIRECT("E"&MATCH("X",A1:A200)))

let me know if you get good results.

Regards,
Barry

My Favorite New Thing:
Dynamic Named Ranges

If you get a response that answers your query or you see a response that explains something for you. Maybe think about "LIKING" that response let the member know he/she has helped another.

• Thanks, iwrk4dedpr, I think this is on the right track. However, I forgot that the formula needs to read info from another sheet.

The formula will be entered on the "Quote" worksheet and read the "Paper Stock" worksheet. The intent is to be able to place an X (in column A) next to the item picked, and then have the name of that item (in column E) carried over to the Quote sheet.

I tried to add 'Paper Stock'! to the formula you provided but apparently not in the right locations.

Clear as mud? Thanks again for your help.

• Ok here it is with your page names

=IF(ISERROR(MATCH("X",Stock!A1:A200)),"",INDIRECT("Stock!E"&MATCH("X",Stock!A1:A200)))

Let me know if this doesn't suit your needs.

Regards,
Barry

My Favorite New Thing:
Dynamic Named Ranges

If you get a response that answers your query or you see a response that explains something for you. Maybe think about "LIKING" that response let the member know he/she has helped another.

• Thank you iwrk4dedpr, it works great! Just what I wanted. You are a big help.

Thank again.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!