= Joining Data with Excel =

<<TableOfContents>>

----



== VLOOKUP ==

== OFFSET-MATCH ==

== INDEX-MATCH ==

== FILTER ==

One way to side-step certain issues with non-unique keys is to instead ''filter'' for rows that match a criteria.

||'''A''' ||'''B'''||'''C'''||'''D''' ||'''E'''||
||Alice   ||1      ||       ||Alice   ||1      ||
||Bob     ||2      ||       ||Danielle||4      ||
||Charlie ||3      ||       ||Ethan   ||???    ||
||Danielle||4      ||       ||        ||       ||

Where the E column contains this formula:

{{{
=FILTER($B$1:$B$10, $A$1:$A$10=D1, "???")
}}}

If there are blank rows, guard the formula:

{{{
=IF(D1<>"", FILTER($B$1:$B$10,$A$1:$A$10=D1,"???"), "")
}}}

This works even if the key in column D is non-unique. If the key in column A is non-unique, then the `FILTER` function will return an array and spill. See [[Excel/Arrays#Spilling|here]] for help on fixing that.



=== FILTER with Multiple AND Conditions ===

The second argument to `FILTER` is functionally a boolean array. To require multiple conditions for a match, enclose conditions in parentheses and multiply them. (Remember that 0 times anything is 0, so ''any'' 0 will cause the value to be filtered out.)

{{{
=FILTER($B$1:$B$10, ($A$1:$A$10=D1) * ($B$1:$B$10<10), "???")
}}}



=== FILTER with Multiple OR Conditions ===

Following from the above, to require any of a set of conditions for a match, enclose conditions in parentheses and add them.

{{{
=FILTER($B$1:$B$10, ($A$1:$A$10=D1) + ($B$1:$B$10<10), "???")
}}}

Note that the returned values are ordered according to their original order, '''''not''''' according to which condition passed.

Also note that you are much more likely to have spilling occur with OR conditions. As above, see [[Excel/Arrays#Spilling|here]] for help on that.



----
CategoryRicottone