Joining Data with Excel
Contents
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 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 here for help on that.