Get field of an array based on second array values

How To
1

Hi there,

I want to create a variable that contains email address(es) if the corresponding value of another array is higher than 0.

For example:

Array-1 is in the format number, number (these are percentage values)
Array-2 is in the format email1@domain.com, email2@domain.com

Desired output:

  • if Array-1 is 100, 0 then output should be email1@domain.com
  • if Array-1 is 0, 100 then output should be email2@domain.com
  • if Array-1 is 50, 50 then output should be email1@domain.com;email2@domain.com (note the semicolon instead of the comma)

I tried with convoluted splits, gets, and ifs, but I got myself into a vortex of confusion. There must be a better way - I’d appreciate any assistance!

Dennis

2

Hello @Dennis_Gee,
Can you please share the proper structure of the Array input that you’re trying with those conditions?
Otherwise please share a screenshot of your implementation which makes more sense to understand how you’re implementing it.

3

The input comes from cell lookups in Google Sheets.

For example:

Cell 1 has a value 100, 0
Cell2 has a value: email1@domain.com, email2@domain.com

image
image950×344 51.8 KB

So, if cell AI = 100, 0, output would be the first email from AH.
If AI = 0, 100 then the output would be the second email from AH.
If AI = 50, 50 then the output should be both emails from AH separated by a semicolon.

4

Hello,
Please try this. Make sure you point out the proper variables.

{{if(2.percentageOwnership = "100,0"; get(split(2.emails; ","); 1); if(2.percentageOwnership = "0,100"; get(split(2.emails; ","); 2); 2.emails))}}

Make_multiple_if_statements
Make_multiple_if_statements1213×913 84.5 KB

We can also achieve this using the SWITCH function which comparatively creates a smaller formula. But I can see that you’re aware of IF so just use it.
I hope this works.

5

Hi @dilipborad , thanks for your reply.

Yes, that was the basic formula I was initially trying but a more feature-rich version. Also, the final statement in the formula mentioned will output the emails comma separated when they need to be semicolon separated.

I would be interested in seeing (and learning from) the split method if that is not too difficult to produce?

6

Hello @Dennis_Gee,
This is tricky.
When there are 50,50 = email1;email2
I’ve used join(split(2.emails; ","); ";") split with in join.

{{if(2.percentageOwnership = "100,0"; get(split(2.emails; ","); 1); if(2.percentageOwnership = "0,100"; get(split(2.emails; ","); 2); join(split(2.emails; ","); ";")))}}

Check this new formula.

I’ve used this old post reference to join by a semicolon(;).