Hey all,
I've been struggling with an issue for far too long, so any help would be appreciated in getting me out of this hole.
From the top:
I have one sheet named 'Policies', with the policy name displayed in column A, and corresponding details about that policy in columns B to X. In another sheet I would like to list the policy names which have aspects that could be improved, in this example using the data from column B and column D. Column B has to be higher than 0 (basically a quantity - to warrant a mention) and column D has to equal false.
I sorted this without a problem using Index and Sumlookup to report back the name of the policy:
{=INDEX(Policies!A3:A100,SUMPRODUCT((Policies!B3:B100>0)*(Policies!D3:D100="FALSE")*ROW(Policies!A3:A100)),0)}
Although there may be multiple policies which match the criteria. How would I manage this? Ideally as the names will be concatenated in another function following this function, so all matching values should be displayed in a single cell separated by commas. I have tried to generate the 'next' matching policy in another cell by using:
{=INDEX(Policies!A3:A100,SUMPRODUCT(((Policies!B3:B100>0)*(Policies!D3:D100="FALSE")*(Policies!A3:A100<>B6))*ROW(Policies!A3:A100)),0)}
with B6 being the destination of the previous lookup, but to no luck.
Any other methods of doing something like this would be appreciated, and please ask away if you need more information.
Thanks so much in advance!
I've been struggling with an issue for far too long, so any help would be appreciated in getting me out of this hole.
From the top:
I have one sheet named 'Policies', with the policy name displayed in column A, and corresponding details about that policy in columns B to X. In another sheet I would like to list the policy names which have aspects that could be improved, in this example using the data from column B and column D. Column B has to be higher than 0 (basically a quantity - to warrant a mention) and column D has to equal false.
I sorted this without a problem using Index and Sumlookup to report back the name of the policy:
{=INDEX(Policies!A3:A100,SUMPRODUCT((Policies!B3:B100>0)*(Policies!D3:D100="FALSE")*ROW(Policies!A3:A100)),0)}
Although there may be multiple policies which match the criteria. How would I manage this? Ideally as the names will be concatenated in another function following this function, so all matching values should be displayed in a single cell separated by commas. I have tried to generate the 'next' matching policy in another cell by using:
{=INDEX(Policies!A3:A100,SUMPRODUCT(((Policies!B3:B100>0)*(Policies!D3:D100="FALSE")*(Policies!A3:A100<>B6))*ROW(Policies!A3:A100)),0)}
with B6 being the destination of the previous lookup, but to no luck.
Any other methods of doing something like this would be appreciated, and please ask away if you need more information.
Thanks so much in advance!
