I am currently making a tardy counter system for my principal as of right now I am having issues with trying to make it so that whenever a new entry is put into the Datasheet it references to the ID sheet and checks to see if it's correct and if it is the cell next to it is blank if it is incorrect it should say error but every time when I try =IF(A2:A="","",ARRAYFORMULA(if(VLOOKUP(A2:A, ID!A1:A,1,FALSE)=A2:A, "", "error")))

it correctly checks but if any are wrong it outputs N/A I'm fine with that on the ones with a value in it but I need to get rid of the "N/A" corresponding to the blank cells

in summary, I need it to count the values that don't match the ID sheet as wrong rather than the blank cells as wrong

I tried multiple if functions but had no good results

also here is a copy

CodePudding user response：

try:

=ARRAYFORMULA(IF(A2:A="","",IFNA(IF(VLOOKUP(A2:A,ID!A:A,1,)=A2:A,),"error")))

CodePudding user response：

Have you tried the ISBLANK() function. Ex. IF(ISBLANK($A2),...

I got this working in google sheets, try it out:

=IF(isblank($A2),"", IF(IFERROR(VLOOKUP($A2, ID!A:A,1,FALSE)=$A2), "", "error"))