INNER JOIN in Google Spreadsheets
I don't know or remember the technical name of what I'm looking for but I think an example will be enough for you to understand exactly what I'm looking for.
Given table A
a x1
b x2
c x1
and Table B
x1 x
x1 y
x1 z
x2 p
x2 z
I Need Table C
a x
a y
a z
b p
b z
c x
c y
c z
I'm looking for a formula or a set of them to get table C
I guess just need to add an extra row on the C table with each value of the first column on the table A for each corresponding value of TableA!Column2 to TableB!Column1 But I can't find how
I think this is a simple SQL Inner Join.
Solution
try:
=ARRAYFORMULA({TRIM(FLATTEN(SPLIT(QUERY(REPT(IF($A2:A="",,$A2:A&"♦"), IFNA(VLOOKUP($B2:$B, QUERY({$D2:$D},
"select Col1,count(Col1) where Col1 is not null group by Col1"), 2, 0))),,9^9), "♦"))), IFNA(VLOOKUP(
TRIM(FLATTEN(SPLIT(QUERY(REPT(IF($A2:A="",,$B2:B&"♦"), IFNA(VLOOKUP($B2:$B, QUERY({$D2:$D},
"select Col1,count(Col1) where Col1 is not null group by Col1"), 2, 0))),,9^9), "♦")))&COUNTIFS(
TRIM(FLATTEN(SPLIT(QUERY(REPT(IF($A2:A="",,$A2:A&"♦"), IFNA(VLOOKUP($B2:$B, QUERY({$D2:$D},
"select Col1,count(Col1) where Col1 is not null group by Col1"), 2, 0))),,9^9), "♦"))),
TRIM(FLATTEN(SPLIT(QUERY(REPT(IF($A2:A="",,$A2:A&"♦"), IFNA(VLOOKUP($B2:$B, QUERY({$D2:$D},
"select Col1,count(Col1) where Col1 is not null group by Col1"), 2, 0))),,9^9), "♦"))), ROW(INDIRECT("O1:O"&COUNTA(
TRIM(FLATTEN(SPLIT(QUERY(REPT(IF($A2:A="",,$B2:B&"♦"), IFNA(VLOOKUP($B2:$B, QUERY({$D2:$D},
"select Col1,count(Col1) where Col1 is not null group by Col1"), 2, 0))),,9^9), "♦")))))), "<="&ROW(INDIRECT("O1:O"&COUNTA(
TRIM(FLATTEN(SPLIT(QUERY(REPT(IF($A2:A="",,$B2:B&"♦"), IFNA(VLOOKUP($B2:$B, QUERY({$D2:$D},
"select Col1,count(Col1) where Col1 is not null group by Col1"), 2, 0))),,9^9), "♦"))))))),
{D2:D&COUNTIFS(D2:D, D2:D, ROW(O2:O), "<="&ROW(O2:O)), E2:E}, 2, 0))})
- Search for a qualifying subarray in a 3d array
- Adding values to a C# array
- Search a 2d array by one column value and return another value from the qualifying row
- Search a multidimensional array by one column value and return another value from the qualifying row
- Get element value from a 2d array row which qualifies because of another column value
- Can I use array_search() to find a whole-row match in a 2d array?
- How to Mark Repeated Entries as True Starting from the Second Occurrence Using NumPy?
- Why arrays are still there when we are actually always using List<T>?
- php - find if an array contains an element
- Doctrine entity object to array
- Determine if an associative array of strings or arrays is equal to another array with the same structure
- Printing array contents in the same line in awk
- Merge two 2d arrays, group by one column and sum another column within each group
- PHP json_decode multiple arrays or array in array to database
- Return the first level key when a column value is found in a 2d array
- Find key of parent in array / PHP
- Find parent key of match in a multidimensional array
- Recursively search a multidimensional array for the parent key of a matched key or value
- Find a parent key in a 2d array
- Get first level key in a 2d array of the first row with a specified value in any element
- Filter a 2d array to only keep rows with a specified value in a specified column
- Access an accompanying value in the first qualifying row of a 2d array
- Get the first level key of a 2d array row with a qualifying value in a specified column
- Get the first level key of the first row containing a specified column value in a 2d array
- Remap values of a flat array based on row data in a 2d array
- Recursively search a multidimensional array for a row with a qualifying column value
- Return values of qualifying row when searching a 2d array
- Get all first level keys from a 2d array where a specified value is found anywhere in the row
- Get the first qualifying whole row from a 2d array
- Recursively search a hierarchical multidimensional array for partial value matches in a column and return values in another column found on the way