Comparing 2 lists for indicating where data appears on both

[MjRmatt]

I have 2 lists of Addresses (lets call them List A and List B) on 2 seperate workbooks, I need to compare the 2 lists and in a fresh column indicate whether the data from List A also appears on List B.

For example:

List A

123 New Street
124 New Street
125 New Street
126 New Street
127 New Street

List B

123 New Street
126 New Street
127 New Street


I want to return either a Y to indicate that 123/126/127 New Street appears on both or a N to indicate that 124 New Street and 125 New Street are not part of list.

I have tried a vlookup but it does not help.

Any suggestions please?

[Rahul Nagar]

Hi,

Please check the attached file.
you can change 123/125/126 into Y

[zbor]

This will give you number where you data match: =MATCH(A1&B1, INDEX(Sheet1!A:A&Sheet1!B:B, 0), 0)

Where your formula is next to data on List B.

To make it faster limit your search list : INDEX(Sheet1!$A$1:$A$1000&Sheet1!$B$1:$B$1000

[zbor]

For Y you can make some if statement, for example:

=IF(ISNUMBER(MATCH(A1&B1;INDEX(Sheet1!A:A&Sheet1!B:B;0);0));"Y";"No data")

(replace ; with ,)

[MjRmatt]

All those solutions are close/correct in theory but in application they dont work.

I have attached a small snippet of what I have to do as an example:

In sheet 1 column C I want an indication that "63 Ashdown Drive" appears in sheet 2 Column B as well (in the actual exercise the 2 tables are in different spreadsheets all together) and that the other addresses are not in sheet 2


If a formula can indicate where the data appears on both lists then I can filter column C.

[zbor]

Here...

Only you need to get rid of double space between words: 63__ASHDOWN DRIVE

[MjRmatt]

I didnt notice the double space, no wonder nothing was working in practice. Thank you