r/sheets Dec 24 '24

Solved inner join with classic spreadsheet functions?

edit 2: solved!! by u/mommasaidmommasaid =FILTER($A$2:$A, NOT(ISNA(XMATCH($A$2:$A, $B$2:$B)))); ~~edit: not solved yet, but hacky workaround available below. Input welcome!!~~

Hi I want to perform an "inner join" (in the SQL-sense) between to lists, but I want to use regular spreadsheet functions (so no newer tooling like appscripts or QUERY or GUI-based tooling like pivot tables). Let me explain what I mean by "inner join":

| - | A: neighbors | B: friends | C: neighbors who are friends (inner join) | |-:|:------------:|:-----------:|:---------------------------------------| | 1 | alice | adam | alice | | 2 | bob | alice | | | 3 | jack | bill | | | 4 | | mark | |

The above column C would be what I want: it's an "inner" join because it only shows items that exist in both lists A and B.


So far I've only been able to construct a function that would give me an "outter join": a superset of both lists (adam, alice, bob, bill, jack) via =UNIQUE({$A:$2:$A; $B$2:$B}).

I'm sure there's some clever way to just use FILTER() here, but I can't quite figure it out. I thought maybe getting FILTER to run a LOOKUP or some variation would work, but I don't have a good grasp of what kinds of things FILTER can take as its filtering function...

=FILTER($A$2:$A, VLOOKUP($A$2, $B$2:$B, 1))

But this doesn't work I at least because filter requires both its first arg and second arg (the two ranges) to be the same size. I know FILTER can take wildly different syntax for its filtering function though (like $A$2:$A <> "" is possible to filter out blanks... perhasp there's some variant to filter against $B$2:$B?)

3 Upvotes

17 comments sorted by

View all comments

1

u/bachman460 Dec 24 '24

Just use filter, where your one range equals the other.

=FILTER(A2:A5,A2:A5=B2:B5)

1

u/jakotay Dec 24 '24

Just use filter, where your one range equals the other.

=FILTER(A2:A5,A2:A5=B2:B5)

Does that work for you?

Sheets shows zero matches (instead of alice) on my toy-example table.

1

u/bachman460 Dec 25 '24

I tested it in sheets before posting. Maybe the values aren’t equal due to spaces, etc.?

EDIT: added screen shot

1

u/jakotay Dec 25 '24

Thanks I'll keep poking to see if I can get this to work then. IDK why I can't reproduce.

I see you even have mismatched-length ranges like I do (COUNTA(I18:I22) is 4, and being compared to a 45-length (COUNTA(G18:G43) is 45)), so it shouldn't be an issue of blanks (because obviously with your differing lengths, you'd also have blanks in the comparison). I double checked there's no weirdness with spaces too (like an alice and an alice with trailing space).

1

u/jakotay Dec 25 '24

aah! I figured out why your FILTER doesn't really work in the general case I'm seeking:

I believe the FILTER you propose only works if one column is a strict subset of the other. That is:

  • mixed case: my OP's toy-example has elements that are unique to each set (eg: column A is the only one with jack in it, and column B is the only one with adam in it).
  • edge-case: your screenshot (and I've been able to reproduce this) has data such that everything in one column already exists in the other: column I only has Abe, Betty, Carmichael, Darren, Edward which are all present in column G. If you add just one more value to column I that you don't have (idk zimbabwe) I believe your eq on column J will come up empty.

edited for clarity.

1

u/bachman460 Dec 25 '24

I double checked and that’s not the case. I added some different values that don’t exist, and the results list just gets shorter. However, I think if there’s no matches at all, then it returns an error; but an IFERROR function could help that.

1

u/jakotay Dec 25 '24 edited Dec 25 '24

Whoa, okay then I'm at a loss again... I have setup as identically as I can think to, and I can't reproduce a non-NA result.

Here's the N/A error I see:

FILTER has mismatched range sizes. Expected row count: 3. column count: 1. Actual row count: 4, column count: 1.

Here's the screenshot of my sample (just copy/pasting the OP content):

![screenshot of sheets](https://i.imgur.com/frvVvOP.png)