Multiple Index Collect in same formula

Justin Mauzy

I need to be able to put 2 Index Collect in the same formula.

This formula works fine but I need to be able also look for July (7) and Index another external reference.

{Site Information Sheet Range 2} = Date column on external sheet

{Site Information Sheet Range 3} = Text/Number column on external sheet

=INDEX(COLLECT({Site Information Sheet Range 2}, {Site Information Sheet Range 3}, [Site ID:]@row, {Site Information Sheet Range 2}, MONTH(@cell) = 8), 1)

I have tried using IF statements and get an error.

=IF(INDEX(COLLECT({Coil Site Information Sheet Range 2}, {Coil Site Information Sheet Range 3}, [Site ID:]@row, {Coil Site Information Sheet Range 2}, MONTH(@cell) = 7), 1), IF(INDEX(COLLECT({Coil Site Information Sheet Range 2}, {Coil Site Information Sheet Range 3}, [Site ID:]@row, {Coil Site Information Sheet Range 2}, MONTH(@cell) = 8), 1)))

Thanks in advance for the help.

Justin Mauzy

I figured it out. I had to add a helper column on the local sheet called Month. I then entered the month for example March would be 3, April 4 and so on... Using IF, INDEX, COLLECT then worked.

Here is the working formula:

=IF(Month@row = "4", INDEX(COLLECT({Site Information Sheet Range 2}, { Site Information Sheet Range 3}, [Site ID:]@row, {Site Information Sheet Range 2}, MONTH(@cell) = 4), 1), IF(Month@row = "5", INDEX(COLLECT({Site Information Sheet Range 2}, {Site Information Sheet Range 3}, [Site ID:]@row, {Site Information Sheet Range 2}, MONTH(@cell) = 5), 1)))

Justin Mauzy

I figured it out. I had to add a helper column on the local sheet called Month. I then entered the month for example March would be 3, April 4 and so on... Using IF, INDEX, COLLECT then worked.

Here is the working formula:

=IF(Month@row = "4", INDEX(COLLECT({Site Information Sheet Range 2}, { Site Information Sheet Range 3}, [Site ID:]@row, {Site Information Sheet Range 2}, MONTH(@cell) = 4), 1), IF(Month@row = "5", INDEX(COLLECT({Site Information Sheet Range 2}, {Site Information Sheet Range 3}, [Site ID:]@row, {Site Information Sheet Range 2}, MONTH(@cell) = 5), 1)))