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

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

@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.

@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

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:

  • 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.

@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: