0

I am trying to build a formula under sheet1 columnM to provide a status for each cell in columnE in sheet1, if the cell in columnE is found in columnJ in sheet2 then return "analysis complete" if not found, if cell in columnE in sheet1 is found in columnJ in sheet3 then return "pendning analysis" if not found then return "log in pending". The "status" should come from the following formula but I must be missing something because I spot checked the status of a few and they were incorrect.

=IF(COUNTIF('Sheet2'!J:J,'Sheet1'!E2),"Analysis Complete",IF(COUNTIF('Sheet3'!J:J,'Sheet1'!E2),"Pending Analysis","Pending Log In"))
allenski
  • 1,652
  • 4
  • 23
  • 39
jabv
  • 1
  • 1
    It is probably data related. there is probably a space or other none printed character that is making it so the cells are not a match. This is looking for an exact non case sensitive match. – Scott Craner Dec 21 '20 at 14:25
  • By spot checked, I meant I copied a few IDs in sheet1 columnE and used "ctrl f" to find if they existed in either sheet2 or sheet3 for those status returning "log in pending" and they did exist. There are no "pending analysis" being returned, only "log in pending" and "analysis complete" – jabv Dec 21 '20 at 14:31
  • I bet your find was set to `contains` and not `exact` match. Again you probably have spaces or other non printed characters in the lookup column. – Scott Craner Dec 21 '20 at 14:34
  • No, it's an exact match and also what I'm finding odd is that it is returning "analysis complete" for IDs that are the "pending analysis" sheet. – jabv Dec 21 '20 at 14:39
  • 1
    it would be easier to help with example data. (see [mcve]) – ashleedawg Dec 21 '20 at 14:43
  • there is nothing wrong with the formula, so the error is in the execution or data. I have tried to guess all that I can guess. You will need to do your own troubleshooting. – Scott Craner Dec 21 '20 at 14:51

0 Answers0