Hi,

I am currently working with 2 sets of data, both data sensitive so I can not share examples, which I need to match.
The novice in me thought "easy there must be an =if contains", which turns out to be =IF(ISNUMBER(SEARCH) but that's fine.

When using this formula it works well if the data in column a1 contains any data from b1 - and I can get it to just produce that data that is matched, leaving the rest. This exactly what I want, apart from it only ever works when the data is in the same row!



How do I get the formula to show data that appears in any row of column b is contained in any row of column a

COLUMN A
A1
1A
3BA
ACC1
4ACC

COLUMN B
1
2
3
4
5

Any help with this is greatly appreciated people!!