Solved: IN() across multiple List()

Austin · 08-27-2020 12:46 PM

I have a list of people and I want to make a slice of everyone that is in any of my performed actions lists. I have deny, review, screen, interview, and offer list of actions my users have performed and those lists are limited to only actions done by those users. I want to know is it before to do OR(In deny list, in review list, in etc.) or IN(person, deny list+review list+screen etc)? This should be a moderately small list as they are gonna be limited to last 30 days for the most part as well but if there is any impact I do want the best formula for this as the rest of the app is gonna hit performance hard as well.

Steve

IN(needle, (haystack1 + haystack2 + ...)) constructs a single list (memory use) from the component lists then scans the entire resulting list for needle. I suspect the scan looks at every element of the list even after finding a match (worst case), but it may instead stop at the first match (best case).

OR(IN(needle, haystack1), IN(needle, haystack2), ...) scans each individual list. When last I asked the developers, I was told that OR() and AND() evaluate all of their component expressions even if a logical decision can be made earlier. If this is still true (worst case), OR() is potentially as bad as using IN() on the combined list. If it’s no longer true (best case), OR() is better than using IN() on the combined list.

IFS(IN(needle, haystack1), TRUE, IN(needle, haystack2), TRUE, ...) manually implements the OR() best case.

View solution in original post

Steve

IN(needle, (haystack1 + haystack2 + ...)) constructs a single list (memory use) from the component lists then scans the entire resulting list for needle. I suspect the scan looks at every element of the list even after finding a match (worst case), but it may instead stop at the first match (best case).

OR(IN(needle, haystack1), IN(needle, haystack2), ...) scans each individual list. When last I asked the developers, I was told that OR() and AND() evaluate all of their component expressions even if a logical decision can be made earlier. If this is still true (worst case), OR() is potentially as bad as using IN() on the combined list. If it’s no longer true (best case), OR() is better than using IN() on the combined list.

IFS(IN(needle, haystack1), TRUE, IN(needle, haystack2), TRUE, ...) manually implements the OR() best case.

Austin

So with IFS I could also search the most likely lists first and the least likely lists last as well since Offer is least likely where as Review and Deny are the most likely list to find them in.
Thanks

Steve

Yep.

Lucinda_Mason

Can I search for TWO needles in ONE haystack? If so, how?

GreenFlux

Austin

OR(IN(Needle1, Haystack1),IN(Need2,Haystack1))
that should work if you want to know if either value exist or if you AND it, it would tell you if both needles are in the haystack. If you’re doing List in List you might want to start looking at List addition,subtraction and COUNT().