search in multiple columns if a specific value exists

[psdileep]

I've data in 47000 rows and 20 columns. I've hidden all columns except two from which I need to do analysis.

I need to search in these two columns and identify if a particular string exists in either of the columns and return 'yes' or 'no'.

From the below example, I want to search for "Apple" in Col 1 and Col 2 and in Col 3, I should have row 2 and row 4 are marked as yes (for that matter some remark which says yes). Because 'Apple" can be found in row 2 and row 4.

Any formula or function I can use?

Col 1	Col2
Apple is red <tbody> </tbody>	I like mangoes <tbody> </tbody>
Orange is orange <tbody> </tbody>	I am a boy <tbody> </tbody>
mango is yellow <tbody> </tbody>	I like apples <tbody> </tbody>
grapes are purple <tbody> </tbody>	I don’t know <tbody> </tbody>

<tbody>
</tbody>

 

[mumps]

Can there be more than one occurrence of the word in either column?

 

[seguin85]

This should work:

=IF(COUNTIF(B2:C2,"*"&"apple"&"*")>0,"yes","no")

Where B2 is column 1 and C2 is column 2

 

[psdileep]

Yes. There is possibility of having the word in either of the columns or in both the columns.

 

[psdileep]

This formula didn't work. I didn't get desired result.

 

[seguin85]

Is it formatted like:

	A	B	C
1	apple is red	i like mangos	yes
2	orange is orange	i am a boy	no
3	mango is yellow	i like apples	yes
4	grapes are purple	i don't know	no

<tbody>
</tbody>

In formula C1 it should be:

[COLOR=#574123]=IF(COUNTIF(A1:B1,"*apple*")>0,"yes","no")[/COLOR]

The copy this down

 

[mumps]

It looks like seguin85's formula should work.

 

[Ryan C]

I don't think it can be done outside VBA. I've been googling for some time now and cant find a method that allows you to check if a value exists in an array (across multiple columns)

 

[jtakw]

Hi,

Chances are, it can be done with Formula, I've done many, depending on the situation.
As this thread is more than 5 years old, start a new thread with your particular question, I'm sure you'll be able to get some assistance.

 

[Ryan C]

That's my bad, while google was almost entirely useless in answering the query without VBA code, turns out the one's I did test didn't work because I accidentally had the wrong range selected.
=countif(array,"*apple*") works, doesn't actually require ctrl+shift+enter either