Excel: Finding data within a row within another row

[Syringer]

That was confusing but hopefully this makes it clear..

Say I have two columns, in column A is:

a
d
f

And in column B is:

a452
b1914
c391
d481
e011
f9444

Basically I want to create a column C that will search all the values within col A that's contained with col B, so the end result would look like this:

a a452 TRUE
d b1914 FALSE
f c391 FALSE
d481 TRUE
e011 FALSE
f9444 TRUE

The 1st, 4th, and 6th columns are true because they contain either an "a", "d", or "f".

Is there formula that can do this?

 

[imported_Dhaval00]

=IF(ISNUMBER(SEARCH(A1,B1)), TRUE, FALSE)

SEARCH is not case sensitive - if you want to perform a case sensitive search, replace SEARCH with FIND. As a side note, ISNUMBER is used because SEARCH and FIND return the starting index of the search term [if found].

 

[Syringer]

That only compares the cell with the cell next to it though.

What I want is to compare each cell in col B to ALL the cells in A, so f9444 will be TRUE because there's an 'f' in Col A.

I tried this but it didn't work: =IF(ISNUMBER(SEARCH(B1,$A$1:$A$3)), TRUE, FALSE)

 

[KLin]

Sounds like you need a vba macro

 

[jersiq]

=SUM(IF(ISERROR(SEARCH($A$1:$A$3,B1)),,1))

And enter it as an array formula - Press Ctrl-Shift-Enter while in the formula bar. This will give the curly braces around the formula. You can enter it and drag fill and it will appear in the subsequent cells as an array formula already, provided you entered it the first time as an array formula.

 

[Kntx]

VLOOKUP