0

My data contains traffic source data from Google Analytics, grouped by customer. For each row, there is a TRANSPOSE(ARRAYFORMULA( function that fills a horizontal array of values from another sheet. Any customers in the source sheet that have multiple rows with different values for traffic source will have an array of two or more values.

enter image description here

The formula I need is in column B. The formula I have so far is:

=ARRAYFORMULA(IF(D2:D="",C2:C,IF(COUNTIF($C:$Z,"Affiliate")>0,"Affiliate","-")))

The required output is an array that returns a value of source based on the condition set in the COUNTIF part of the formula. In this case I want "Affiliate" to be returned if it is present in the corresponding row. If it is not present, but "Referral" is present, return "Referral", otherwise return "-". Currently the formula returns "Affiliate" if it is present anywhere in the $C:$Z array, and is therefore not row-based.

Is there a way to change the array formula to search each row independently and return values based on the defined conditions?

Link to sample data with desired output

Edit: included link to sample data with required output. Also changed required output to include multiple values:

In this case I want "Affiliate" to be returned if it is present in the corresponding row. If it is not present, but "Referral" is present, return "Referral", otherwise return "-". Currently the formula returns "Affiliate" if it is present anywhere in the $C:$Z array, and is therefore not row-based.

Improve this question
1

1 Answer 1

Reset to default
1

Use byrow(), join() and match(), like this:

=byrow( 
  C2:Z, 
  lambda( 
    row, 
    ifs( 
      len(join("", row)) = 0, iferror(1/0), 
      ifna(match("Affiliate", row, 0)), "Affiliate", 
      ifna(match("Referral", row, 0)), "Referral", 
      true, "-" 
    ) 
  ) 
)

The results look like this:

Customer Attribution Source array
Dweezil Affiliate Affiliate d e f g
Moon Unit - c d e f g
Ziggy Affiliate c d e Affiliate g

See byrow(), join() and match().

Improve this answer
3
  • Thanks - that worked perfectly. I wonder - I've just edited the question to include multiple values. How would that affect the formula? Sample data also linked in the question
    – bonko99
    Feb 23 at 13:53
  • Edited the answer per your new requirements. Please do not present new requirements after you have received an answer. Ask only one question per post. Post a new question instead.
    – doubleunary
    Feb 23 at 18:31
  • Thanks - my bad I'm new to this!
    – bonko99
    Feb 24 at 8:39

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.