How do I combine multiple INDEX Collect formulas in one Formular?

Thomas Fechter

Hello, dear support team,

I have a question about combining several Index Collect functions. 

The Goal is: I want to get the Payment Terms into the Cash Out section of the Cashflow.

As you can see in the picture below;

25% of Product #1 has to be paid in the calendar week 8. 50% of P#1 in CW9 and the remaining 25% in CW10. This information is Put in manually.

The formula that gets this information is as follows:

=IFERROR(INDEX(COLLECT({01 BoM #1 Value}, {01 BoM Cost Code}, [Nr. ]@row, {01 BoM #1 CW}, [PW 1]1, {01 BoM #1 Year}, [PW 1]2), 1), "---")

it searches for the Ammount of Money by the criteria of Cost Code, Calender Week and Calender Year. But it can only get the information of on Payment.

You can see the result and the Formula in the pictures below:

Now my question is: how can I Create a Formula that will search for all three payment terms and will display only the correct Payment term with the corresponding calendar week and Year?

My try was:

=IFERROR(INDEX(COLLECT({01 BoM #1 Value}, {01 BoM Cost Code}, [Nr. ]34, {01 BoM #1 CW}, [PW 2]1, {01 BoM #1 Year}, [PW 2]2), ), INDEX(COLLECT({01 BoM #2 Value}, {01 BoM Cost Code}, [Nr. ]@row, {01 BoM #2 CW}, [PW 2]1, {01 BoM #2 Year}, [PW 2]2), ), "---")

But it Displays #Unparseable

What would a correct combination look like?

Paul Newcome

Your closing parenthesis are off. Build it out one at a time like so:

=INDEX(COLLECT(1st one), 1)

=IFERROR(above formula, INDEX(COLLECT(2nd one), 1))

=IFERROR(above formula, INDEX(COLLECT(3rd one), 1))

=IFERROR(above formula, "---")

Basically just keep wrapping around the outside until you have as many as you need.

=INDEX(COLLECT(1st one), 1)

=IFERROR(INDEX(COLLECT(1st one), 1), INDEX(COLLECT(2nd one), 1))

=IFERROR(IFERROR(INDEX(COLLECT(1st one), 1), INDEX(COLLECT(2nd one), 1)), INDEX(COLLECT(3rd one), 1))

=IFERROR(IFERROR(IFERROR(INDEX(COLLECT(1st one), 1), INDEX(COLLECT(2nd one), 1)), INDEX(COLLECT(3rd one), 1)), "---")

Ramzi

Looks like your #Unparseable error may be due to this extra comma with no parameter to follow. Remove it completely or add a column number for the Index function.

Thomas Fechter

Hello Mr. Ramzi,

thanks for the suggestion and help, but the new function results in "#incorrect argument set"

I tried three versions:

#1 without any comma in Row Number #34:

=IFERROR(INDEX(COLLECT({01 BoM #1 Value}, {01 BoM Cost Code}, [Nr. ]@row, {01 BoM #1 CW}, [PW 2]1, {01 BoM #1 Year}, [PW 2]2)), INDEX(COLLECT({01 BoM #2 Value}, {01 BoM Cost Code}, [Nr. ]@row, {01 BoM #2 CW}, [PW 2]1, {01 BoM #2 Year}, [PW 2]2)), "---")

#2 with the Column number (36 and 40) of the asked amount:

=IFERROR(INDEX(COLLECT({01 BoM #1 Value}, {01 BoM Cost Code}, [Nr. ]@row, {01 BoM #1 CW}, [PW 2]1, {01 BoM #1 Year}, [PW 2]2), 36), INDEX(COLLECT({01 BoM #2 Value}, {01 BoM Cost Code}, [Nr. ]@row, {01 BoM #2 CW}, [PW 2]1, {01 BoM #2 Year}, [PW 2]2), 40), "---")

#3 with an index function/number: i use 0 and 1 - both resultet in "#incorrect argument set"

=IFERROR(INDEX(COLLECT({01 BoM #1 Value}, {01 BoM Cost Code}, [Nr. ]@row, {01 BoM #1 CW}, [PW 2]1, {01 BoM #1 Year}, [PW 2]2), 1), INDEX(COLLECT({01 BoM #2 Value}, {01 BoM Cost Code}, [Nr. ]@row, {01 BoM #2 CW}, [PW 2]1, {01 BoM #2 Year}, [PW 2]2), 1), "---")

For any clarification, I will show below the Sheet (BoM) that the information has to be taken out of. In row 1 you can see the column numbers of the amount € that I have to get in the Cashflow Plan.

Do I need to include an AND OR function? or some sort of other differentiator?

Paul Newcome

You need to add another IFERROR to the front.

Right now your formula is saying to run the first INDEX/COLLECT. If there is an error with the first one, then run the second one. That leaves your final piece of , "---" just hanging out there.

Try this:

=IFERROR(IFERROR(INDEX(COLLECT({01 BoM #1 Value}, {01 BoM Cost Code}, [Nr. ]@row, {01 BoM #1 CW}, [PW 2]1, {01 BoM #1 Year}, [PW 2]2), 1), INDEX(COLLECT({01 BoM #2 Value}, {01 BoM Cost Code}, [Nr. ]@row, {01 BoM #2 CW}, [PW 2]1, {01 BoM #2 Year}, [PW 2]2), 1)), "---")

Thomas Fechter

Hello Paul,

That was very helpful. It works now ( for the second Payment term)!

Now I have a new question. How do I add a third, fourth, fifth, and sixth payment term?

Given that I created Columns in the BoM for the next Payment terms.

I tried to add the IFERROR and a new line for the third payment term but it shows now "Incorrect Argument Set"

=IFERROR(IFERROR(IFERROR(INDEX(COLLECT({01 BoM #1 Value}, {01 BoM Cost Code}, $[Nr. ]@row, {01 BoM #1 CW}, [PW 3]$1, {01 BoM #1 Year}, [PW 3]$2), 1), INDEX(COLLECT({01 BoM #2 Value}, {01 BoM Cost Code}, $[Nr. ]@row, {01 BoM #2 CW}, [PW 3]$1, {01 BoM #2 Year}, [PW 3]$2), 1), INDEX(COLLECT({01 BoM #3 Value}, {01 BoM Cost Code}, $[Nr. ]@row, {01 BoM #3 CW}, [PW 3]$1, {01 BoM #3 Year}, [PW 3]$2), 1))), "---")

Is this maybe getting too long?

Another side question: is there maybe another "easier" way to do this or is this the correct way?

Paul Newcome

Your closing parenthesis are off. Build it out one at a time like so:

=INDEX(COLLECT(1st one), 1)

=IFERROR(above formula, INDEX(COLLECT(2nd one), 1))

=IFERROR(above formula, INDEX(COLLECT(3rd one), 1))

=IFERROR(above formula, "---")

Basically just keep wrapping around the outside until you have as many as you need.

=INDEX(COLLECT(1st one), 1)

=IFERROR(INDEX(COLLECT(1st one), 1), INDEX(COLLECT(2nd one), 1))

=IFERROR(IFERROR(INDEX(COLLECT(1st one), 1), INDEX(COLLECT(2nd one), 1)), INDEX(COLLECT(3rd one), 1))

=IFERROR(IFERROR(IFERROR(INDEX(COLLECT(1st one), 1), INDEX(COLLECT(2nd one), 1)), INDEX(COLLECT(3rd one), 1)), "---")

Thomas Fechter

Yes!

This works now for all Payment terms.

Thank you very much!

Paul Newcome

Happy to help. 👍️