Returning a statement based on whether the values in one list can be found in another list

[Javersoon]

Hallo everybody

I will try to do this as short and brief as possible.

In sheet1 cell D26 I wanna have a statement that shows whether the values from list A (a data sheet only containing some ID's) can be found in list B or not (a sheet of "masterdata" containing all ID's)

In short:

I have some ID's in Sheet 2 column F and I have some ID's in sheet3 column A. What I wanna have is an equation in Sheet1 Cell D26 where I test whether or not the ID's in sheet2 column F is part of the masterdatafile containing the ID's (sheet3 column A). I wanna use a formula that returns some kind of statement/text string telling me, whether my IDs in sheet2 can actually be found in my masterdata Sheet3.

I hope you have the information needed in order du help me out. Otherwise dont hesitate to write!

Kind regards

[CARACALLA]

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Do not try to use the Paperclip icon, as this does not work on this forum.

[Javersoon]

Here is an example

[Javersoon]

There is about 1000 ID's in my actual sheet2 and just about 2000 ID's in my sheet3

[CARACALLA]

	A	B
25		ID SHEET2 NOT IN SHEET3
26	ID's match?	45
27		23
28		1234
29		3655
30		5678
31		6976
32		43565
33		758657

Sheet 1


B26=IFERROR(INDEX(Sheet2!$F$18:$F$1000,SMALL(IF(ISNA(MATCH(Sheet2!$F$18:$F$1000,Sheet3!$A$2:$A$2000,0)),IF(Sheet2!$F$18:$F$1000<>"",ROW(Sheet2!$F$18:$F$1000)-ROW(Sheet2!$F$18)+1)),ROWS(Sheet2!$F$18:Sheet2!F18))),"")

Control+shift+enter

copy across and down

[Javersoon]

CARACALLA

Thank you very much.
The thing is that, that equation does not meet my requirements. The equation, as I see it (so please correct me if I am wrong), generates a list in sheet1 containing all the ID's from list2 that does not appear in sheet3.

What I want instead is just a simple statement in sheet1 Cell D26 that says something in the line of "Yes" or "no" if there is a match or if there is no match. That means that if just one ID in sheet2 is missing from sheet3, I want that statement. Not for each ID, but just one statement in Cell D26 in sheet1; kind of like a resume.

Does it makes sense?

I just wanna give the reader a quick overview of whether the list in sheet2 contains ID that is not represented in Sheet3.

If you some other way this can be done, please let me know

[CARACALLA]

	A	B
25		ID SHEET2 NOT IN SHEET3
26	ID's match?	8

Sheet1


B26=SUMPRODUCT((ISNA(MATCH(Sheet2!F18:F1000,Sheet3!A2:A2000,0)))*(Sheet2!F18:F1000<>""))

[Javersoon]

Thank you. That worked wonderfully!

[CARACALLA]

You are welcome