to see if a unique id in one sheet sits anywhere in another sheet

[Joschiedog]

I have two sheets of data, where column A of both sheets have a unique ID. I would like to see if any of the unique ID's in sheet two are also in sheet one. I've tried VLookups, but this only picks those up where they match exactly on the same row number. My issue is, the Unique ID's my fall on both sheets, but not necessarily on the same row, and that the entries in the two sheets may contain different amounts of data, so in Sheet 1 there may be 200 entries, but in sheet 2, only 150 entries.
The way i'd prefer to represent the returns is in a column. rather than creating a single cell and searching individually, as my ultimate aim is to show if there are any instances of the ID's showing in both sheets in Tableau.


If anyone has any solutions, I'd be most grateful.

 

[Scott T]

Excel 2010
	A
2	ID1
3	ID2
4	ID3
5	ID4
6	ID5
7	ID6
8	ID7
9	ID8
10	ID9
11	ID10
12	ID11
13	ID12
14	ID13
15	ID14
16	ID15
17	ID16
18	ID17
Sheet1

Excel 2010
	A	B
2	ID9	On sheet1
3	ID8	On sheet1
4	ID7	On sheet1
5	ID6	On sheet1
6	ID5	On sheet1
7	ID4	On sheet1
8	ID3	On sheet1
9	ID2	On sheet1
10	ID14	On sheet1
11	ID13	On sheet1
12	ID12	On sheet1
13	ID11	On sheet1
14	ID10	On sheet1
15	ID1	On sheet1
16	id77	NOT on sheet1
Sheet2

Cell Formulas
Range	Formula
B2	=IF(ISNUMBER(MATCH(A2,Sheet1!$A$2:$A$18,0)),"On sheet1","NOT on sheet1")
B3	=IF(ISNUMBER(MATCH(A3,Sheet1!$A$2:$A$18,0)),"On sheet1","NOT on sheet1")
B4	=IF(ISNUMBER(MATCH(A4,Sheet1!$A$2:$A$18,0)),"On sheet1","NOT on sheet1")
B5	=IF(ISNUMBER(MATCH(A5,Sheet1!$A$2:$A$18,0)),"On sheet1","NOT on sheet1")
B6	=IF(ISNUMBER(MATCH(A6,Sheet1!$A$2:$A$18,0)),"On sheet1","NOT on sheet1")
B7	=IF(ISNUMBER(MATCH(A7,Sheet1!$A$2:$A$18,0)),"On sheet1","NOT on sheet1")
B8	=IF(ISNUMBER(MATCH(A8,Sheet1!$A$2:$A$18,0)),"On sheet1","NOT on sheet1")
B9	=IF(ISNUMBER(MATCH(A9,Sheet1!$A$2:$A$18,0)),"On sheet1","NOT on sheet1")
B10	=IF(ISNUMBER(MATCH(A10,Sheet1!$A$2:$A$18,0)),"On sheet1","NOT on sheet1")
B11	=IF(ISNUMBER(MATCH(A11,Sheet1!$A$2:$A$18,0)),"On sheet1","NOT on sheet1")
B12	=IF(ISNUMBER(MATCH(A12,Sheet1!$A$2:$A$18,0)),"On sheet1","NOT on sheet1")
B13	=IF(ISNUMBER(MATCH(A13,Sheet1!$A$2:$A$18,0)),"On sheet1","NOT on sheet1")
B14	=IF(ISNUMBER(MATCH(A14,Sheet1!$A$2:$A$18,0)),"On sheet1","NOT on sheet1")
B15	=IF(ISNUMBER(MATCH(A15,Sheet1!$A$2:$A$18,0)),"On sheet1","NOT on sheet1")
B16	=IF(ISNUMBER(MATCH(A16,Sheet1!$A$2:$A$18,0)),"On sheet1","NOT on sheet1")

 

[Joschiedog]

Many thanks for this Scott. I have just plugged it in and tried and it does work up to a point, but not fully functioning as predicted. I like your formula and can read how it goes, so will play around with it to try and get it to work. It might be that I have my data set in tables so they automatically expand depending on how much data is being copied into each list.

 

[mikerickson]

In Sheet1 B1 put the formula =COUNTIF(Sheet2!A:A, A1)

If it returns 0, no match in sheet2, othewise, the ID is also in sheet2

 

[Joschiedog]

Thanks Mike, that seems to do the trick. Will use this and am able to move on now. Great help