|
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 column(s) to match on:
-1 = match columns on column headers. The first row of Left and of Right is assumed to contain column header labels. 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 (match left column 1 with right column 1, left column2 with right column2 …)
Single Item or array or range: Numbers are used as column number(s) and text is used as header label(s) in the first row to match on.
If a single row or a single item then these column number(s)/header label(s) will be used for both Left and Right.
For example:
The number 2 results in matching column 2 on left with column 2 on right.
“Brand” looks for the column containing a header label of “Brand” in the first row of both left and right, but they do not have to be in the same column.
The single row array {2,4} matches using both column 2 and column 4 in both left and right
If 2 rows then the first row gives the column numbers/header labels for Left and the second row gives the corresponding column numbers/header labels from Right.
For example:
The two row array {2;4} matches left column 2 with right column 4.
2 row 2 column array {1,3;2,4} matches left column 1 with right column 2 and left column 3 with right column 4.
2 row 1 column array {“Brand”;”Alternate Brand”} would look for a column header of “Brand” in Left and “Alternate Brand” in Right.
Note: you cannot match a column label from left/right with a numeric column from left/right.
Skip_LeftCols & Skip_RightCols (Optional: default="")
Optional: default = “”. Columns from Left or Right to omit from the output. If numeric gives the zero-based column number(s) to omit. Zero means column 1 and 1 means column 2 etc. 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.
|
