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.
This question is related to
vba
excel
excel-2007
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.
You can use VLOOKUP, but this requires a wrapper function to return True or False. Not to mention it is (relatively) slow. Use COUNTIF or MATCH instead.
Fill down this formula in column K next to the existing values in column I (from I1 to I2691):
=COUNTIF(<entire column E range>,<single column I value>)>0
=COUNTIF($E$1:$E$99504,$I1)>0
You can also use MATCH:
=NOT(ISNA(MATCH(<single column I value>,<entire column E range>)))
=NOT(ISNA(MATCH($I1,$E$1:$E$99504,0)))
You can try this. :) simple solution!
=IF(ISNUMBER(MATCH(I1,E:E,0)),"TRUE","")
Source: Stackoverflow.com