|
MATCHES
Finds the matched rows (join) in two ranges/arrays/tables/lists and joins them.
MATCHES can handle one-to-one, one-to-many, and many-to-many joins.
The matches can be done on one or more chosen columns.
Matches are not case-sensitive so “ID” matches with “id”.
Numbers are treated as text, so 42 matches with both “42” and 42.
You can specify which columns from the Left range or array to match with which columns from the Right range or array. Columns that are not used to match will still appear in the output.
MATCHES Syntax
MATCHES (Left, Right, Match_Type, Match_Cols, Skip_LeftCols, Skip_RightCols, No_Match)
Left (Required)
The Left range/array/list/table.
Right (Required)
The Right range/array/list/table.
Match_Type (Optional: default = 4)
The type of output match requested:
4 = All rows from Left with any matching rows from Right
5 = Only matching rows from Left with their matching rows from Right
6 = All rows from Right with any matching rows from Left
7 = Only matching rows from Right with their matching rows from Left
Match_Type is optional; if omitted or empty, the value 4 will be used.
Match_Cols (Optional: default = -1)
The columns to match on:
-1 = match columns on column headers. The first row of Left and of Right is assumed to contain column headers. The column headers do not have to be in the same sequence. Any columns whose headers that do not match are ignored by the matching process but are shown in the output.
0 = match on all columns in sequence
Array or range = Column numbers or header names to match on. If a single row then these column numbers/header names will be use for both Left and Right. If 2 rows then the first row gives the column numbers/header names for left and the second row gives the corresponding column numbers/header names from Right. For example {1,3;2;4} matches column 1 in Left with column 2 from Right and column 3 from Left with column 4 from Right, and {“Brand”;”Alternate Brand”} would look for a column header of “Brand” in Left and “Alternate Brand” in Right.
Skip_LeftCols & Skip_RightCols (Optional: default=0)
Optional: default = 0. Columns from Left or Right to omit from the output. 0 means show all columns. A single-row range or array giving column numbers or header labels.
No_Match (Optional: default= #Null)
Value to use for rows that do not match.
|
