Try this in cell C1 (delete all other values below):
=arrayformula({"Status";if(A2:A<>"",substitute(substitute(iferror(vlookup(A2:A,filter(sheet2!A:B,countifs(sheet2!A:A,sheet2!A:A,row(sheet2!A:A),">="&row(sheet2!A:A))=1),2,0),),1,"Issued"),0,"In-Stock"),)})
To handle items in sheet1 that are not in sheet2, you can add a value in the iferror function (the first "In-Stock" below):
=arrayformula({if(A2:A<>"",substitute(substitute(iferror(vlookup(A2:A,filter(sheet2!A:B,countifs(sheet2!A:A,sheet2!A:A,row(sheet2!A:A),">="&row(sheet2!A:A))=1),2,0),"In-Stock"),1,"Issued"),0,"In-Stock"),)})
This is responsible for getting the dataset from sheet2:
filter(sheet2!A:B,countifs(sheet2!A:A,sheet2!A:A,row(sheet2!A:A),">="&row(sheet2!A:A))=1)
This part gets the instance number of each duplicate value in sheet2!A:A:
countifs(sheet2!A:A,sheet2!A:A,row(sheet2!A:A),">="&row(sheet2!A:A))
Where there are duplicate values, ">=" gets the one furthest down the sheet. Changing it to "<=" will get the first instance at the top of the sheet.
Within filter, countifs(sheet2!A:A,sheet2!A:A,row(sheet2!A:A),">="&row(sheet2!A:A))=1) filters the column to show just 1 instance of each value in sheet2!A:A.
Looking at your example sheet, you have these values in row 6, 7, 8, so the first instance (ascending or descending) will be 0:
1230E 0
1230E 1
1230E 0
If you're looking for a different logic, like 1 trumps 0, then you'll need to apply a sort on the data before performing the vlookup. Something like:
=arrayformula({if(A2:A<>"",substitute(substitute(iferror(vlookup(A2:A,unique(sort(sheet2!A2:B,1,1,2,0)),2,0),"In-Stock"),1,"Issued"),0,"In-Stock"),)})
