These functions join two input ranges according to matching keys. Even if keys can not be matched, elements from the first table (or second table if you use RIGHTOUTERJOIN) are still displayed.

Syntax

=MM_LEFTOUTERJOIN(Key1, Output1, Key2, Output2)

• Key1: First range key

• Output1: First range

• Key2: Second range key

• Output2: Second range to join with Output1

=MM_RIGHTOUTERJOIN(Key1, Output1, Key2, Output2)

• Key1: First range key

• Output1: First range to join with Output2

• Key2: Second range key

• Output2: Second range

Remarks

• If the function can not match keys from a line, this line will be displayed.

• Warning: For each line of your first/second range, the function matches keys only one time. See Example 3 on INNERJOIN article.

Example 1: LEFTOUTERJOIN

Let us consider the two following tables:

You can see range keys at the first column of each table.

The formula to join those 2 tables according to their matching keys is the following:

=MM_LEFTOUTERJOIN(B2:B4;C2:D4;G2:G5;H2:J5)

Here, key "c" on Table 1 is not included in key range from Table 2. However, as explained in "Remarks" section, elements of the corresponding line in Table1 will still be displayed as you can see in the following table.

The resulting table displays, in the first place, elements from Table1, then elements from Table2.

You can notice that the 3 cells at the right from value "F" are empty. It is due to the fact that key "c" is not in range key from Table 2.

Example 2: RIGHTOUTERJOIN

With same inputs as in Example 1 (Table 1 and Table 2), the function MM_RIGHTOUTERJOIN will join Table 1 with Table 2 with this following formula:

=MM_RIGHTOUTERJOIN(B2:B4;C2:D4;G2:G5;H2:J5)

Here, the order will be différent, the formula will return the following table, with, in the first place, elements from Table 2, then elements from Table 1.

You can see here that the two last lines have empty cells. It is explained by the fact that key "e" and "r" does not have a matching key from Table 1.