INDEX/DISTINCT with multiple criteria

Sarah_lee123

Hello,

I am trying to create a formula that will pull in a distinct text with multiple criteria. I do have a helper column.

The original formula I used works in a different scenario where only one criteria is met. =INDEX(DISTINCT({CLT}), Number@row)

But now I want to create one that pulls in "SegAddy" when "RTRD" box IS NOT checked.

I am trying things like this, and turning up unsuccessful "incorrect argument"

COLLECT(DISTINCT({SegAddy}), Number@row, {RTRD}, 0)

INDEX(DISTINCT({SegAddy}), Number@row, {RTRD}, 0)

Please help me formulate the correct argument to meet my goal of pulling in the "SegAddy" only when the "RTRD" box (from source sheet) is not checked.

Thanks!!

Darren Mullen

@Sarah_lee123

Distinct() takes a range as an argument, and collect returns a range, so if you use collect() to collect the range of values you want, then wrap it in distinct() then that should work.

So, distinct(collect()). Then you could put that into your INDEX() function.