Find if column contains value from another column?

Question

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.

Use the Vlookup Formula. Check the Excel help :) – Siddharth Rout Jul 03 '12 at 16:55 — Siddharth Rout, Jul 03 '12 at 16:55

score 57 · Accepted Answer · answered Jul 03 '12 at 17:07

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.

score 42 · Answer 2 · edited Oct 24 '17 at 00:20

42

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)))

edited Oct 24 '17 at 00:20

Stevoisiak

23,794
27
122
225

answered Jul 03 '12 at 17:47

JimmyPena

8,694
6
43
64

2

also +1. Note that as MATCH returns either a number or an error then rather than NOT with ISNA you can simply use ISNUMBER (or even COUNT which will return a 1/0 result) – barry houdini Jul 03 '12 at 18:05
@barryhoudini Good point, there are several different combinations of formulas that would work. – JimmyPena Jul 03 '12 at 18:15

score 6 · Answer 3 · edited Oct 12 '15 at 09:17

6

You can try this. :) simple solution!

=IF(ISNUMBER(MATCH(I1,E:E,0)),"TRUE","")

edited Oct 12 '15 at 09:17

James Webster

31,873
11
70
114

answered Oct 12 '15 at 08:41

taosio

61
1
1

Find if column contains value from another column?

3 Answers3

Linked