ExcelBanter - LOOKUP QUESTION

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - LOOKUP QUESTION (https://www.excelbanter.com/excel-discussion-misc-queries/148431-lookup-question.html)

Hi,

If I have 2 sheets both with a list of unique identifiers on it, what is the
best way to run a query which will check to see if I have all the IDs in one
that are in the other:

i.e.
Sheet 1: (being the master sheet which will always be correct)

A
B
C
D

Sheet 2: ( A data sheet reporting on Sheet 1)

A
B
C

I woul dlike a formula in sheet 2 to return something like, "ID Missing" or
better still: "ID "D" is missing"

thanks!

Kahuna,
Try this

="ID
"&IF(COUNTA(Sheet1!A3:A21)=COUNTA(Sheet2!A3:A21)," ",INDIRECT("Sheet1!"&ADDRESS(COUNTA(Sheet1!A2:A20) +1,1)))&" is Missing"

Change range to suit

Regards
Peter

"kahuna" wrote:

Hi,

If I have 2 sheets both with a list of unique identifiers on it, what is the
best way to run a query which will check to see if I have all the IDs in one
that are in the other:

i.e.
Sheet 1: (being the master sheet which will always be correct)

A
B
C
D

Sheet 2: ( A data sheet reporting on Sheet 1)

A
B
C

I woul dlike a formula in sheet 2 to return something like, "ID Missing" or
better still: "ID "D" is missing"

thanks!