Alternative to Countif due to closed workbooks

[pcarle1]

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 does, put "Y" as the result in this field. I removed the super long file location in the formula to make it easier on the eyes, and put "filelocation" in place as placeholder. Currently this formula works, but only if I have the other workbook open. I really need this function to be able to operate independently without the need for the other workbook open as it can be pretty large and I cannot include it in this workbook as a separate sheet.

=IF(OR(ISBLANK($L16)),"",IF(COUNTIF('filelocation/[MVR Monitoring Batch File (MASTER).xlsx]Sheet1'!$A:$A,[@EmployeeID]), "Y", ""))

I hope I explained this clear enough, and without the ability to post a picture of the spreadsheet I feel like this may be tough to understand. I am open to answer any questions that may assist. Thanks!

[#2 · Accepted solution]

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),"",...

[#3]

Hello and thank you for the response. I totally forgot about utilizing the match function for this. So far I have not gotten it to populate a "Y" when I know it should, but I have been so swamped today I have not had the chance to really dive in it. If I have more issues with it, I will respond with what I learn.

In response to the first portion, this was created as a way to return the value "" if no value was found in cell $L16. I had been accustomed to putting IF(OR(ISBLANK for a while as this was something I learned not realizing the OR( is not needed,

[#4]

@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)...

[#5]

@L z. 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

[#6]

@pcarle1 

If Source1 (your Master as I understand) is an Excel file where the Employee ID are always 5 digits with leading zeros it's highly probable those IDs are stored a Text values in which case the following does it here:

=IF(ISNUMBER(MATCH(TEXT([@EmployeeID],"00000"), 'PathToYour[Master.xlsx]Sheet1'!$A:$A, 0)), "y", "")

[#7]

This fixed it! Thank you so much for the assistance on this one. I am marking the topic as resolved.

[#8]

@pcarle1 

Glad this worked. Thanks for providing feedback & marking

Nice EOD...