Hello! I'm using an INDEX/MATCH formula to return the "Type" associated with a specific "Email." I'm getting a no match error when an Email value is first repeated. After the first repeat, the formula no longer functions. I've included my actual and desired outputs below.
Both the form submissions data and the summary data are located in the same sheet. Ideally, I'd like:
- For the Summary section to be located in a different sheet (but I'm having trouble with the distinct email formula when it's outside the main sheet).
- For the Distinct Type formula to continue to function, even with repeat form users (ie. same email address).
- For the Distinct Type formula to update to the most recently submitted value, in case a user switches from one type to another (ex. if a visitor becomes a patron)
Thank you for your help!
Actual Output:
Desired Output:
Formulas being used…
Number:
=MATCH([Ticket Number]@row , [Ticket Number]:[Ticket Number], 0)
Distinct Email:
=INDEX(DISTINCT(Email:Email), Number@row )
Distinct Type:
=INDEX(Type@row , MATCH([Distinct Email]@row , Email@row , 0))
Count:
=COUNTIF(Email:Email, @cell = [Distinct Email]@row )
