Search code examples
excelexcel-2007

Find if column contains value from another column?

I have two columns.
Column E extends up to 99504 (values) and column I extends to 2691 (values).
Both columns contains filenames with extension.

Something like this:

E I
Filename_A Filename_B
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) exist in column E (heading Filename_A).

If true, say TRUE in another column let's say column K.

Solution

  • 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.