Index/Small Functions

Hi Everyone,

I hope everyone is well.

I have a problem where I have five team members maintaining individual trackers of their on-going projects. These trackers are individual files which I set-up, and each of the team members updates their file as required. These trackers populate report cards, which are a separate tab in each of their workbooks.

Of the five of them, three of them still work fine. The other two, both have a problem where the arrays are not returning any data, not even errors, in their report cards. I've gone through and verified that the number formatting matches on all of them and I can even take the data from one of the workbooks that is not returning any values, paste it directly into a properly functioning workbook, and it works as expected.

The function that drives each of these is as below,

{=IFERROR(INDEX(Proj.Tracker!$A$1:$A$776,SMALL(IF((Proj.Tracker!$AN$1:$AN$776=$AA$5)*(Proj.Tracker!$C$1:$C$776=$B$1),ROW(Proj.Tracker!$A$1:$A$776)),ROW()-6),1),"")}

I have similar formulas in two other locations in the workbook, and all return nothing.

Does anyone know of a common problem, or setting, that may be causing this? To date, I've just been copying the information from one of the non-functioning workbooks into a functioning workbook, and saving over the copy of the file, but it is getting too manually intensive.

Any thoughts?

Thanks


RichMW

It may be hard to diagnose the problem with just an isolated formula. It would probably increase your chances of a response if you could upload a copy of (a) a correctly functioning workbook and (b) a workbook that is not functioning correctly. Desensitize if necessary.


From your earlier posts I think you know how to upload.

Thanks for the reply.

I know how to upload, but I do not know how I can desensitize the information contained in the workbook without re-writing the entire sheet, which could potentially eliminate the problem anyway.

I'm hoping someone who uses the index/small function regularly may be aware of potential causes or limitations.

For additional information, I can make each condition of the index/match portion work, but when it is combined into the small function, it stops working, despite the same formula working in the other workbooks of the other people. So the data type must be correct in terms of matching properly.

Thanks
RichMW

If the columns the Small(If functions are looking at contain formulae, make sure that there are no formulaic errors in them.