Find if value in column A contains value from column B?

Nemo picture Nemo · Jul 3, 2012 · Viewed 253.8k times · Source

I have two columns- column E which extends upto 99504(values) and column I which extends to 2691(values).Both the columns contains filenames with extension.

Something like this

E               I
TSL_groups.mrk  pcbx_report.mrk
abcd.mrk        jhuo.mrk

and so on...

I want to find if the files in column I (heading Filename_B) exists in column E(heading Filename_A). If true, say TRUE in a new column let's say column K.

How do I do that? TIA.

Answer

Brad picture Brad · Jul 3, 2012

You could try this

=IF(ISNA(VLOOKUP(<single column I value>,<entire column E range>,1,FALSE)),FALSE, TRUE)

-or-

=IF(ISNA(VLOOKUP(<single column I value>,<entire column E range>,1,FALSE)),"FALSE", "File found in row "   & MATCH(<single column I value>,<entire column E range>,0))

you could replace <single column I value> and <entire column E range> with named ranged. That'd probably be the easiest.

Just drag that formula all the way down the length of your I column in whatever column you want.