Forum Discussion

pcarle1's avatar
pcarle1
Copper Contributor
Jun 19, 2023
Solved

Alternative to Countif due to closed workbooks

Hello all, I am looking to see what alternative I can use to countif for the formula below. Essentially what I am doing is checking another workbook to see if an employee id # exists there and if it...
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Jun 20, 2023

    Hi pcarle1 

     

    An alternative could be MATCH:

    =IF(ISNUMBER(MATCH([@EmployeeID], 'filelocation/[MVR Monitoring Batch File (MASTER).xlsx]Sheet1'!$A:$A, 0)), "y", "")

     

    Not quite sure I understand what the first portion of your complete formula is supposed to do:

    =IF(OR(ISBLANK($L16)),"",...

    as the following should be enough:

    =IF(ISBLANK($L16),"",...

     

Lorenzo's avatar
Lorenzo
Silver Contributor
Jun 20, 2023

pcarle1 

So far I have not gotten it to populate a "Y" when I know it should

Assuming you're right data quality is probably the source of the issue (GIGO)...

pcarle1's avatar
pcarle1
Copper Contributor
Jun 20, 2023

Lorenzo I think I found the issue, the data between the two sources seems to be formatted differently. For example, in one spreadsheet the ID # can be 6, but in the other sheet the same employee would be 00006 as our ID numbers are all listed as a 5 digit number from 00001-99999. I think my new problem is that both of these two sources are unable to be adjusted by me as they come out of data lakes. Is there any way to have the MATCH function still work for these? 

source 1 - source 2:

00006 = 6

00354 = 354

01000 = 1000

12345 = 12345

 

Resources