# Index Matching & CountA function

1. ## Index Matching & CountA function

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.

2. ## Re: Index Matching & CountA function

I don't think I'm entirely clear on what you're requesting, but if my understanding that you want to fix the counting in C3:G3 is correct, try the following formula in C3:

=COUNTIFS(Sheet!X\$3:X\$100,"<>"&"",Sheet!\$J\$3:\$J\$100,\$A3)

Fill right and then down. I'm not clear on what's supposed to be happening in column H, so if that's the issue, please elaborate. I'm also not clear on why X:AB is repeated in AC:AG, but your existing formulas looked only at X:AB, so mine do as well. Take a look at the attachment to see if C:G are calculating as desired:

3. ## Re: Index Matching & CountA function

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!!!!

4. ## Re: Index Matching & CountA function

Ah, that's a bit clearer. It sounds like you could smooth out the links between the workbooks. Here's a better explanation than I can provide of the various ways to control external link updates:

https://support.office.com/en-us/art...3-dd357fe0e653

I don't have extensive experience with linked workbooks, so I don't know how troublesome it is to have a formula rely on a reference to an external (closed) source. If the formulas aren't working through the link the way that you want, it might be worth your time to create a "Sheet" sheet in the same workbook as your "Vacancies" sheet and tie that sheet to your external raw data page. That way instead of having the formula pull from a linked workbook, the formula is pulling from another sheet in the same workbook that merely mimics the data from the linked book. That approach might make your updates cleaner.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1