This is difficult to put into words, so I apologize in advance if I butcher it. :slight_smile: In my app, I’m cross-referencing between our Active section and the Parks section. When a user visits Home > Active > Archery, they are presented with info about archery but also a list of parks from our Parks section that have this feature (in this case, archery). I’ve got these 1:1 relationships working without issue—both ADA Accessibility and Archery work as intended. However when I get to Active > Baseball, I run into trouble. For Baseball, I need to combine two similar park features into a single inline list under Active. For example, for Active > Baseball I need to display all the parks with the feature “baseball field” and “baseball field (lighted)” from the Parks sheet and I don’t know how to write that relational formula? FYI, the parks listed right now under Active > Baseball are only baseball fields and don’t include the parks with lighted baseball fields. I need to combine these. As you can see in my database, I’m using the following cell formula to complete the multiple relation for Archery: Parks with Archery=Parks:archery:Multiple Is there a different way to write this formula with some syntax to achieve what I need like: Parks with Baseball=Parks:baseball field&baseball field (lighted):Multiple Here’s a link to my app and a separate link to the database. Thank you in advance for any assistance…much appreciated! App: https://maricopahealthyforum.glideapp.io Database: https://docs.google.com/spreadsheets/d/1aS1lp5McQkQbtaxYQOVs_J_RIUazI5zguL1B1e0Wh8g/edit?usp=sharing [01%20PM]

@gannonatwork Add a column after each of your columns that you need either or both to be true. Then set the reference to that column. Here is the formula to put in row A of that column. ={"baseball";ARRAYFORMULA(IF(O2:O,TRUE,IF(P2:P,TRUE,FALSE)))} And a screenshot of the result. [image]

@George_B Sorry for the delay, life has got in the way. I appreciate you getting back to me and the suggestion. I did implement this but I’m getting the following error: Array result was not expanded because it would overwrite data in Q9. Any thoughts? FYI, while the screenshot doesn’t show cell Q9 it is blank and has not data. Your feedback is greatly appreciated! Thx [50%20AM]

Just empty/delete all the cells in that column, Q2 to the last row. It may look blank but trust me it has data, maybe a space.

@George_B Hello. Sorry for the back and forth, but I’m still experiencing issues. Even after removing all content from cells O2 to O692, when I paste the formula you provided into cell O2 it still kicks out the following error: Result was not automatically expanded, please insert more rows. Once the formula is placed, it also creates an additional 50,000 rows in the sheet. After googling this, it appears the issue is due to the formula being open ended and not a “closed range” expression. As is obvious from this thread, I don’t know anything about writing array formulas but wondering if you might be able to tell me how to rewrite it so it doesn’t attempt to calculate beyond row O692? FYI, I did try: ={"baseball";ARRAYFORMULA(IF(O2:O692,TRUE,IF(P2:P692,TRUE,FALSE)))} This fixed the error issue and didn’t create any rows beyond O692, but the calculation doesn’t run correctly: [array-formula-issue] As you can see, it writes baseball in Q2. It appears the formula is working as required, but because it writes baseball in Q2 all of the remaining formula results are one row behind. For example, Q4 shows TRUE because P3 is TRUE even though O4 and P4 are FALSE. Here’s a link to the sheet again: https://docs.google.com/spreadsheets/d/1aS1lp5McQkQbtaxYQOVs_J_RIUazI5zguL1B1e0Wh8g/edit?usp=sharing Is there something I’m missing or should the formula be adjusted in some way? As always, I appreciate the insight and your time. Thanks!

Take a close look at where the formula is on my original post. It’s in row 1. I will admit that I said row A instead of row 1, so that may have confused you. It also does not have any ending row number. The last O and P don’t have a number following them. ={"baseball";ARRAYFORMULA(IF(O2:O,TRUE,IF(P2:P,TRUE,FALSE)))} When you put it in row 2, that explains why it said you needed more rows because it was trying to continue to add a row after the current row (if that makes any sense).

BTW, this is the post that talks about this row 1 technique for arrayformulas. [image] Using ARRAYFORMULA()'s in Row 1 of your Spreadsheet Project Showcase I can’t take credit for this tip but I just found out about this trick/tip by reading a post here. So the “Normal” way of using an ARRAYFORMULA function is to use it in row 2 of a given column. You just have to be careful not to accidentally deleted it, or have a user delete it. So the next idea was to put it in row 2 but not put any other data in the row and then hide or minimize that row. Basically your data for the non ARRAYFORMULA columns would start on row 3. Then I saw the post I’ll l…

@George_B Sorry for the delay getting back to you. I appreciate you hanging with me and providing some clarification. Yes, I didn’t understand the formula needed to be placed in Row 1 but have since fixed that. So I ended up using the formula you provided with my closed-range modification and once it was deployed in the correct cell, it works like a charm. Thanks for taking time to help me out…much appreciated! :+1:

Match Multiple Relations --- One Column to Two or More

Ask for Help

George_B October 24, 2019, 5:29pm 7

BTW, this is the post that talks about this row 1 technique for arrayformulas.

Topic		Replies	Views
Relation from multiple values (Data) Feature Requests	4	716	January 25, 2020
Match multiple relations - stuck Ask for Help	4	369	November 4, 2022
How to create a relation which compares with multiple columns in other sheet Ask for Help	9	1388	December 3, 2019
Creating Relation with a Relation and Column Ask for Help	6	883	December 28, 2020
Convert an array (from match multiple relation) into a single column Ask for Help	6	1383	September 30, 2020

Match Multiple Relations --- One Column to Two or More

Related topics