Index Matching & CountA function

Please find workbook attached.
Help 1.xlsx
Hi all,

Ok here's what I'm trying to do. On the vacancies tab I have five core positions and 10 possible other vacant positions. I need a function to look up the ID, find that ID on the "sheet" tab and count 1 if that position is vacant or not (by counting whether or not there is text in that field).

The function I currently have written works well ONLY IF the index matching is able to find the code see row 7, tab "Vacancies". Row seven is an example of the current function returning the right values.

However, the issue becomes when the function cannot find the referenced ID on the sheet tab. In this case, the function returns a value that makes it appear as if individual programs all have 14 vacancies, instead of returning a value of 0 because the ID was not found.

I'm sorry this is a little technical and maybe confusing, but any help you could provide are GREATLY appreciated.

Again, see workbook attached to play with.

Hi - Thank you for your message.

Let me clarify. On my end, the two tabs, vacancies and sheet, are actually two different workbooks. The sheet tab is just data in any order. The vacancies workbook is just returning results from the sheet workbook to facilitate other analysis.

I am seeking a function that will look up the ID in the vacancies workbook (cells A3:A32), look up that code in the sheet workbook (cells J1:J100), and return a value of 1 if there is any text found on the same row as the ID found in cells J1:J00, in column X1:100. The data in columns x1:x100 is text, not numbers, as you can see.

My current function (counta + index/matching) returns a positive value if it cannot find the ID to index match, which is a false positive. It should return a 0 value because there was no code to reference, and hence, no vacancy for that program. Index matching is necessary because the id might be in any order in the raw data file (sheet tab/workbook).

Your function works well, but only if both workbooks are open. Primarily I wont be using the sheets tab at all, its just raw data. If I use your function and only have one workbook open, the vacancies workbook, it returns a #Value error. I didn't upload two workbooks at first because honestly, I didn't know it would make a difference.

Sorry this is confusing but thank you for the help!!!!