I have a formula where I have it look at two dropdown values (c1 and d1) and bring in relevant data where those two values are found in two columns in 'Master Line List' worksheet.
it works as it should, but I added in a new value labeled "All" in d1 dropdown. If user selects "All" then bring in all statuses for that agent (ignore blanks for status column in 'Master Line List'). I have an expected result in the table starting in columns G:K.
Here is what I've tried:
=FILTER(
IF(
AND(D1 = "All", ISNUMBER(MATCH(C1, 'Master Line List'!I:I, 0))),
'Master Line List'!A:I,
CHOOSECOLS('Master Line List'!A:I, 3, 6, 9, 7, 1)
),
IF(
D1 = "All",
ISNUMBER(MATCH(C1, 'Master Line List'!I:I, 0)),
AND(
'Master Line List'!I:I = C1,
'Master Line List'!G:G = D1
)
),
'Master Line List'!G:G <> "", // Excluding rows with blank in column G:G
BYROW(
'Master Line List'!F:F,
LAMBDA(
Σ,
OR(
BYCOL(
SPLIT(Σ, CHAR(10)),
LAMBDA(
Λ,
ISBETWEEN(
--LEFT(Λ, FIND(" ", Λ)),
G2,
H2
)
)
)
)
)
)
)
Any ideas what I could be doing wrong?
You may try:
=filter(choosecols('Master Line List'!A:I,3,6,9,7,1), 'Master Line List'!I:I=C1, if(D1="All",'Master Line List'!G:G<>"",'Master Line List'!G:G=D1),
byrow('Master Line List'!F:F,lambda(Σ,or(bycol(split(Σ,char(10)),lambda(Λ,isbetween(--left(Λ,find(" ",Λ)),G2,H2)))))))
**fully not matching with the given expected output for ALL