Can anyone teach me how I can return a value from a column if multiple criteria are met where if:
- a cell in a column of another sheet contains a word in a cell (referenced)
- the same row in another column contains
SUBMITTED
- the same row in another column contains a text in a cell (referenced)
I tried doing this:
=IF(AND(Sheet1!AE2:AE="*"&B4&"*",Sheet1!G2:G="SUBMITTED",Sheet1!C2:C=D3),Sheet1!D2:D)
but this doesn't return a value. So, I tried using INDEX
& MATCH
using an array formula like this:
=ArrayFormula(INDEX(Sheet1!D2:D,MATCH(1,(Sheet1!AE2:AE="*"&B4&"*")*(Sheet1!G2:G="SUBMITTED")*(Sheet1!C2:C=D3),0)))
but somehow this returns an error:
Did not find value "1" in
MATCH
evaluation.
Am I doing something wrong with my MATCH
? I'm not sure what's going on here.
And also would this be heavy if I were to do this to 10k cells? Would a script/using QUERY
be better in returning these fields?
QUERY
is "heavier" solution.ARRAYFORMULA
does not understandAND
formula, therefore, every statement/criterion needs to be converted into 0/1 logic.IF
formula does not understand"*"
you need to useFILTER
withSEARCH
for that. to return cell found on the corresponding row you need to useVLOOKUP
.QUERY
won't return you cells on the corresponding rows -QUERY
will bulk it down