1

I need to populate column A in sheet two based on multiple columns in sheet one.

For example, here are two of multiple conditions:

  • If columns A,B,C,D (of sheet 2) are all 5/6 then populate corresponding row in sheet one with "mid".

  • If columns A,B,C,D (of sheet 2) contain at least one 3 and L,M,O contain all 0s, populate "low".

I believe using SWITCH would make the most sense, unless someone can reccommend a simpler approach?

My main issue is with the syntax of writing this, I am getting a formula parse error:

=SWITCH(Sheet 1!G2:G&K2:K,ISBETWEEN(5,6),"mid")

Sheet 1 
A B C D E F G H I J K L M N O 
2 2 3 2               0 0 0 0
5 5 6 6

In row one of my example sheet 2 would get "mid" and row 2 would get "low"

jmoerdyk
  • 5,544
  • 7
  • 38
  • 49
  • share a copy / sample of your sheet with an example of the desired output – player0 Aug 23 '22 at 17:02
  • sheet 1 is in the question should this be formatted differently? –  Aug 23 '22 at 17:06
  • Please don't vandalize your posts. By posting on the Stack Exchange network, you've granted a non-revocable right, under the [CC BY-SA 4.0](https://creativecommons.org/licenses/by-sa/4.0/) license, for Stack Exchange to distribute that content (i.e. regardless of your future choices). By Stack Exchange policy, the non-vandalized version of the post is the one which is distributed, and thus, any vandalism will be reverted. If you want to know more about deleting a post please see: [How does deleting work?](https://stackoverflow.com/help/what-to-do-instead-of-deleting-question). – jmoerdyk Aug 29 '22 at 20:28

1 Answers1

0

try:

=ARRAYFORMULA(
 IF( 4=LEN(REGEXREPLACE(FLATTEN(QUERY(TRANSPOSE(A1:D5),,9^9)), "[^5-6]+", )), "mid", 
 IF((4=LEN(REGEXREPLACE(FLATTEN(QUERY(TRANSPOSE(L1:O5),,9^9)), "[^0]+", )))*(REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(A1:D5),,9^9)), "3")), "low", )))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thanks for taking the tie to write this I didn't know it would get so complex. It needs to populate Sheet 2 with Sheet 1 answers so should pasting the formula into sheet 2 and just adjusting your formula to Sheet 1!A1:D5 be working? It isn't for me just want to confirm this is correct and I will try get it working –  Aug 24 '22 at 12:18
  • @signupwithfacebook yes, `A1:D5` would be `Sheet1!A1:D5` or `'Sheet 1'!A1:D5` – player0 Aug 24 '22 at 14:43