1

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?

Improve this question
1
  • 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
    – user0
    Mar 10, 2019 at 17:19

1 Answer 1

Reset to default
0 

=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, ))

0

Improve this answer
5
  • 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.
    – Amirul Faiz
    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)
    – user0
    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?
    – Amirul Faiz
    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?
    – Amirul Faiz
    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
    – user0
    Mar 12, 2019 at 10:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.