Forum Discussion
copy cell
Currently i'm filtering betvine multiple rows
i would like to do it in just one row whit multiple criteria from E1,G1,I1
If I understand what you're saying, the formula would be this:
=FILTER(Sheet1!A:B,(Sheet1!A:A=Sheet2!E1)+(Sheet1!A:A=Sheet2!G1)+(Sheet1!A:A=Sheet2!I1))
The + sign between criteria effectively means match A:A with E1 OR G1 OR I1.
If you want to get sexy, here's the same formula using the new LET function.
=LET(
Srce,Sheet1!A:B,
MtSrce,Sheet1!A:A,
FILTER(Srce,(MtSrce=Sheet2!E1)+(MtSrce=Sheet2!G1)+(MtSrce=Sheet2!I1))
)
Here's what LET does; in effect it's saying
LET "Srce" stand in for Sheet1!A:B in the final formula
LET "MtSrce" stand in for Shhet1!A:A in the final formula
now do the final formula using those variables
And you could make the named variables there even shorter.
I was trying this but it don't work
=FILTER(Sheet1!A:B,(IF(E1>1,Sheet1!A:A=Sheet2!E1,""))+(IF(F1>1,Sheet1!A:A=Sheet2!F1,"")))
Interesting. If you put anything into G1--try "-" for example, or even simply a space--and the formula still works, without returning 0. G1 then isn't empty, but it's a criterion that nothing meets. So FILTER shows nothing for that criterion.
I'm sure there's some kind of way to use IF, or some other conditional but that would get convoluted.
Not knowing what your real world application is, my "workaround" solution would be to make sure G1 always has something in it, even if it's nonsense.