INDEX COLLECT
Hello
I have an 'Internal Resource Rates' sheet set up which shows Service Line and Role: on here I have a UK and US rate for each of these roles:
My resourcing sheet  below  looks at the above via a formula: the 'Internal Rate' value changes based on the 'Currency' value:
I have created this formula and it returns the US $ value correctly:
=IFERROR(IF(Location@row = "US", (INDEX(COLLECT({Internal Resource Rates  US $}, {Internal Resource Rates  Service Line}, CONTAINS([Service Line]@row, @cell), {Internal Resource Rates  Internal Role}, [Internal Role]@row), 1))), "")
When I add (what I think) should be an additional argument to return the UK value when the locations isnt US, I get an error:
=IFERROR(IF(Location@row = "US", (INDEX(COLLECT({Internal Resource Rates  US $}, {Internal Resource Rates  Service Line}, CONTAINS([Service Line]@row, @cell), {Internal Resource Rates  Internal Role}, [Internal Role]@row), (INDEX(COLLECT({Internal Resource Rates  UK £}, {Internal Resource Rates  Service Line}, CONTAINS([Service Line]@row, @cell), {Internal Resource Rates  Internal Role}, [Internal Role]@row), 1)))), "")
Am I missing the obvious?! I'm relatively new to formulas btw!
Best Answer

Hey @1970Sarah
When you added the additional Index/Collect, you needed to add an additional row index parameter (the 1 at the end of the INDEX clause) to complete the new Index/Collect
Try this
=IFERROR(IF(Location@row = "US", INDEX(COLLECT({Internal Resource Rates  US $}, {Internal Resource Rates  Service Line}, CONTAINS([Service Line]@row, @cell), {Internal Resource Rates  Internal Role}, [Internal Role]@row),1), INDEX(COLLECT({Internal Resource Rates  UK £}, {Internal Resource Rates  Service Line}, CONTAINS([Service Line]@row, @cell), {Internal Resource Rates  Internal Role}, [Internal Role]@row), 1)), "")
Does this work for you?
Kelly
Answers

Hey @1970Sarah
When you added the additional Index/Collect, you needed to add an additional row index parameter (the 1 at the end of the INDEX clause) to complete the new Index/Collect
Try this
=IFERROR(IF(Location@row = "US", INDEX(COLLECT({Internal Resource Rates  US $}, {Internal Resource Rates  Service Line}, CONTAINS([Service Line]@row, @cell), {Internal Resource Rates  Internal Role}, [Internal Role]@row),1), INDEX(COLLECT({Internal Resource Rates  UK £}, {Internal Resource Rates  Service Line}, CONTAINS([Service Line]@row, @cell), {Internal Resource Rates  Internal Role}, [Internal Role]@row), 1)), "")
Does this work for you?
Kelly

Kelly  thankyou! That has worked perfectly! Thank you very much for your help :)

Hi @Kelly Moore,
I've been working on a similar formula the the one you helped Sarah with but I can't seem to get it right. Here is where I am so far:
=IF(Ancestors@row = "1", INDEX(COLLECT({QALICB Servs15 Bill Amt Range}, {Transaction Helper ID Range}, CONTAINS([QLICI ID]@row, @cell), {Bill Group Range}, CONTAINS([Bill Group]@row, @cell), 1)), “”)
With this formula I am getting an "Incorrect Argument" returned.
I want to return the value from QALICB Servs15 Bill Amt Range from Sheet 1 based on a match of QLICI ID and Bill Group  two criteria. I also need to have an IF statement that indicates what column should be used based on Ancestors. If Ancestors = 1, I will compare the range {Transaction Helper ID Range} to QLICI ID, but if it is Ancestors = 2, I will compare the {Roles QLICI Range} to QLICI ID.
The formula above is my attempt to include the IF statement and the two criteria for the Index(Collect). Once correct, this formula needs to be modified to include the addition IF statement for Ancestors@row = 2. If Ancestors does not equal 1 or 2, the formula can return "".
I'd greatly appreciate your help in getting this formula to work! I just have not been able to get it on my own.
Thanks!
Ann

Hi @Genevieve P.  You have been so kind to help me with past formula woes. Any insight on my current issue with this IF / INDEX(COLLECT) / CONTAINS combination?
Thank you!
Ann @Ann Hannan

Hey @Ann Hannan
Try this.
=IF(Ancestors@row = 1, INDEX(COLLECT({QALICB Servs15 Bill Amt Range}, {Transaction Helper ID Range}, CONTAINS([QLICI ID]@row, @cell), {Bill Group Range}, CONTAINS([Bill Group]@row, @cell)), 1), “”)
I moved a parenthesis to close off the COLLECT function.
You will add the second IF statement where the double quotes are now. If you need any help, if you give me the second IF, I can add it to the formula.
Kelly

Thank you so much, Kelly! That worked. Those pesky parens. I knew it was something like that, but I'd hit my limit. I truly appreciate your quick response.
Ann

Hi @Kelly Moore ,
I hope you don' t mind a followup question. I've made a lot of progress with your help and now my formula looks like this:
=IF([Year Group]@row = "Years 15", (IF(Ancestors@row = 1, INDEX(COLLECT({QALICB Servs15 Bill Amt Range}, {Roles Transaction ID Range}, [QLICI ID]@row, {QALICB Servs 15 Bill Group Range}, CONTAINS([Bill Group]@row, @cell)), 1), IF(Ancestors@row = 2, INDEX(COLLECT({QALICB Servs15 Bill Amt Range}, {Roles QLICI ID Range}, [QLICI ID]@row, {QALICB Servs 15 Bill Group Range}, CONTAINS([Bill Group]@row, @cell)), 1)))), "n/a")
I've realized though that I would love to have a SUM(CHILDREN()) on the rows where Ancestors=0.
For these rows, the Year Group will be blank, but that is my first condition. I'm not sure where to add the Sum function. I thought it should apply to the very first IF statement but maybe I have them in the wrong order. I'd like it to work out like this:
If Ancestors = 0 then Sum(Children)
If Ancestors = 1 and Year Group = Years 15  then Index Collect with Transaction ID Range, if not then "n/a"
If Ancestors = 2 and Year Group = Years 15  then Index Collect with QLICI ID Range, if not then "n/a"
You'll see above that since Years 15 was a shared criteria, I added it first. Maybe that won't work if I want something different for Ancestors=0.
Again I really appreciate your solutions to these problems! It seems like there should be just a simple way to stick that Sum in there but where?!?!?
Thanks!
Ann

Hey Ann
Happy to help. I have a few questions for clarity. Is it possible for a screenshot this always helps the community. When you say you want the Children, is this the equivalent to the Collect criteria in your Level1 IF statement? Or is it the sum of both Level 1 and Level 2? I'm assuming you want the sum of your {QALICB Servs15 Bill Amt Range}?
Here's the equation with both 1 and 2 criteria. Does this give you what you need?
=IFERROR(IF(Ancestors@row = 0, SUM(COLLECT({QALICB Servs15 Bill Amt Range}, {Roles Transaction ID Range}, [QLICI ID]@row, {QALICB Servs 15 Bill Group Range}, CONTAINS([Bill Group]@row, @cell)), COLLECT({QALICB Servs15 Bill Amt Range}, {Roles QLICI ID Range}, [QLICI ID]@row, {QALICB Servs 15 Bill Group Range}, CONTAINS([Bill Group]@row, @cell))), IF([Year Group]@row = "Years 15", IF(Ancestors@row = 1, INDEX(COLLECT({QALICB Servs15 Bill Amt Range}, {Roles Transaction ID Range}, [QLICI ID]@row, {QALICB Servs 15 Bill Group Range}, CONTAINS([Bill Group]@row, @cell)), 1), IF(Ancestors@row = 2, INDEX(COLLECT({QALICB Servs15 Bill Amt Range}, {Roles QLICI ID Range}, [QLICI ID]@row, {QALICB Servs 15 Bill Group Range}, CONTAINS([Bill Group]@row, @cell)), 1))), "n/a")), "n/a")
Kelly

Hi @Kelly Moore,
Thanks for your reply. When I tried the formula, it returned a value of $0.00 for the top level parent  Ancestor=0.
Currently the 1st level Child already includes a Sum of its Children which is coming from the other table. Therefore, I only want to Sum the "direct" children (not grandchildren :)) of the Ancestor=0 rows.
Here is a screenshot. The blue shaded rows should include a Sum of just the gray shaded rows.
Does that help?
Thanks,
Ann

Aah. I wasn't sure if the hierarchy was built on your destination sheet or just on the source sheet.
=IF(Ancestors@row = 0, SUM(CHILDREN([Bill QALICB Servs 15]@row)), IF([Year Group]@row = "Years 15", (IF(Ancestors@row = 1, INDEX(COLLECT({QALICB Servs15 Bill Amt Range}, {Roles Transaction ID Range}, [QLICI ID]@row, {QALICB Servs 15 Bill Group Range}, CONTAINS([Bill Group]@row, @cell)), 1), IF(Ancestors@row = 2, INDEX(COLLECT({QALICB Servs15 Bill Amt Range}, {Roles QLICI ID Range}, [QLICI ID]@row, {QALICB Servs 15 Bill Group Range}, CONTAINS([Bill Group]@row, @cell)), 1)))), "n/a"))
Does this work for you?
Kelly

Thank you, @Kelly Moore !
For a cleaner design, I adjusted the sheet since my last question. The formula no longer needs to check to see if the [Year Group]@row = "Years 15". It only needs to match the QLICI ID and the Bill Group for the Index(Collect). I tried to adjust the formula you sent as follows:
=IFERROR(IF(Ancestors@row = 0, SUM(CHILDREN([Bill QALICB Servs 15]@row)), IF(Ancestors@row = 1, INDEX(COLLECT({QALICB Servs 15 Bill Amt Range}, {Roles Transaction ID Range}, [QLICI ID]@row, {QALICB Servs 15 Bill Group Range}, CONTAINS([Bill Group]@row, @cell)), 1), IF(Ancestors@row = 2, INDEX(COLLECT({QALICB Servs 15 Bill Amt Range}, {Roles QLICI ID Range}, [QLICI ID]@row, {QALICB Servs 15 Bill Group Range}, CONTAINS([Bill Group]@row, @cell)), 1), "")), "n/a"))
Unfortunately, it didn't work. I took the formula that I had working for Ancestors 1 and 2, and added the IF / SUM statement at the front. It is retuning "BLOCKED" for Ancestors 0 and 1, and "INCORRECT ARGUMENT" for Ancestors 2. All the references are the same and were working previously.
I'll keep trying! Please let me know if you see a easy fix!
Thanks,
Ann

Nevermind, @Kelly Moore !
I found the out of place parentheses! Formula is working now!!!
Thank you so much for your help. With advisors like you this is fun instead of infuriating. :)

Let's swap the Sum for a SUMIFS
IF(Ancestors@row = 0, SUMIFS(CHILDREN([Bill QALICB Servs 15]@row, CHILDREN([Bill QALICB Servs 15]@row, ISNUMBER(@cell)))
Also, check my parentheses. Follow the color pattern of the parens in the formula. Or share a screenshot of the colored formula.

phew! So glad it's working.
Help Article Resources
Categories
Check out the Formula Handbook template!