# Return a value from a column if multiple criteria are met

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?

• using `QUERY` is "heavier" solution. `ARRAYFORMULA` does not understand `AND` formula, therefore, every statement/criterion needs to be converted into 0/1 logic. `IF` formula does not understand `"*"` you need to use `FILTER` with `SEARCH` for that. to return cell found on the corresponding row you need to use `VLOOKUP`. `QUERY` won't return you cells on the corresponding rows - `QUERY` will bulk it down Mar 10, 2019 at 17:19

## 1 Answer

``````=ARRAYFORMULA(IF(
(IFERROR(VLOOKUP(Sheet1!AE2:AE,
FILTER(Sheet1!AE2:AE,
SEARCH("*"&B4&"*", Sheet1!AE2:AE)), 1, 0), )<>"")*
(Sheet1!G2:G="SUBMITTED")*
(Sheet1!C2:C=D3), Sheet1!D2:D, ))``````

• Thank you for this! is it possible that I return only 1 cell, and the first cell it matches only? because this formula returns the range, but only the matched cell pops up. but it still leaves a gaping hole in the data. Mar 12, 2019 at 3:55
• try: `=INDEX(QUERY(ARRAYFORMULA(IF( (IFERROR(VLOOKUP(Sheet1!AE2:AE, FILTER(Sheet1!AE2:AE, SEARCH("*"&B4&"*", Sheet1!AE2:AE)), 1, 0), )<>"")* (Sheet1!G2:G="SUBMITTED")* (Sheet1!C2:C=D3), Sheet1!D2:D, )),"where Col1 is not null"),1,1)` Mar 12, 2019 at 3:59
• This worked beautifully. I'm guessing the QUERY is to clean the null values in the column, and INDEX is to only get the 1 value? Mar 12, 2019 at 4:49
• Somehow changing the `B4` doesn't change the value of the search. It returns the same previous cell. Would changing the index value capture the correct cell? Mar 12, 2019 at 5:52
• yes for query and yes for index. Index could. try to remove index and see how cells change and make sure you are after right first value Mar 12, 2019 at 10:12