Find row number based on criteria

[alfordtp]

I'm trying to create a "find" feature for a workbook.

Lets say I have a spreadsheet with 10 columns. I am looking for the word "apple" in any of the cells in those 10 columns. It may appear in the 2nd column or the 8th column, or any other column. It also most likely will not be the first or only word in the cell (We have a "title" column, a "summary" column, and a "details" column).

Is there a function to put in column 11 that would search all cells in that row and mark (with an "X" or something) if that row contains "apple" somewhere in that row?

Thanks!

 

[BiocideJ]

This solution uses an array formula so you will have to hit CTRL+ENTER after you type it so that is has {} around it and calculates correctly.

=IF(SUM(IF(ISERROR(FIND("apple",A5:J5)),0,1))>0,"X","")

If apple appears anywhere in the row from columns A:J then the cell with this formula will show an X otherwise it will be blank.

 

[T. Valko]

I'm trying to create a "find" feature for a workbook.

Lets say I have a spreadsheet with 10 columns. I am looking for the word "apple" in any of the cells in those 10 columns. It may appear in the 2nd column or the 8th column, or any other column. It also most likely will not be the first or only word in the cell (We have a "title" column, a "summary" column, and a "details" column).

Is there a function to put in column 11 that would search all cells in that row and mark (with an "X" or something) if that row contains "apple" somewhere in that row?

Thanks!

One way...

=IF(COUNTIF(A1:J1,"*apple*"),"X","")

 

[BiocideJ]

One way...

=IF(COUNTIF(A1:J1,"*apple*"),"X","")

Woah, learned something new myself on this question. I didn't realize you could use wildcard characters with COUNTIF. I'm assuming you can use them with SUMIF too.

Very cool and much simpler formula.

 

[T. Valko]

Woah, learned something new myself on this question. I didn't realize you could use wildcard characters with COUNTIF. I'm assuming you can use them with SUMIF too.

Yes.

Functions that support wildcards:

AVERAGEIF - Excel 2007 and later
AVERAGEIFS - Excel 2007 and later
COUNTIF - all versions of Excel
COUNTIFS - Excel 2007 and later
HLOOKUP - all versions of Excel
MATCH - all versions of Excel
SEARCH - all versions of Excel
SUMIF - all versions of Excel
SUMIFS - Excel 2007 and later
VLOOKUP - all versions of Excel

Very cool and much simpler formula.

 

[alfordtp]

Awesome Guys!! i figured there was an easy way, just didnt know how to do it across a whole row.

 

[T. Valko]

Awesome Guys!! i figured there was an easy way, just didnt know how to do it across a whole row.

Good deal. Thanks for the feedback!