Find if value in column A contains value from column B

Question

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

User · Answer

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  lt entire column E range gt   lt single column I value gt   gt 0  COUNTIF  E 1  E 99504  I1  gt 0   You can also use MATCH    NOT ISNA MATCH  lt single column I value gt   lt entire column E range gt      NOT ISNA MATCH  I1  E 1  E 99504 0

User · Answer

You can try this     simple solution    IF ISNUMBER MATCH I1 E E 0    TRUE

User · Answer

You could try this   IF ISNA VLOOKUP  lt single column I value gt   lt entire column E range gt  1 FALSE   FALSE  TRUE    -or-   IF ISNA VLOOKUP  lt single column I value gt   lt entire column E range gt  1 FALSE    FALSE    File found in row      amp  MATCH  lt single column I value gt   lt entire column E range gt  0     you could replace  lt single column I value gt  and  lt entire column E range gt  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

[vba] Find if value in column A contains value from column B?

Examples related to vba

Examples related to excel

Examples related to excel-2007