JOIN

Description

Performs an inner left join on two ranges.

Syntax

JOIN(

primary_table

,

key_column

,

join_range

,

join_key_column

, [

has_headers

], [

ignore_case

])

primary_table

: The first table to join. This range can be a matrix, range, or result of a data set transform operation.

key_column

: The column number in the primary_table expression that acts as the primary key for the join operation. The function numbers columns starting from 1.

join_range

: The second table to join. This range can be a matrix, range, or result of a data set transform operation.

join_key_column

: The column number in the join_range expression that acts as the key for the join operation. The function numbers columns starting from 1.

has_headers

: Not used.

ignore_case

: If TRUE, the function ignores the case of the text when comparing with other key values. If FALSE, the function considers case. The default is FALSE.

Example

This is the original workbook. The data to join is in two different sections:

	A	B	C	D
25	Salesperson	Customer	Product	Revenue
26	Jeffrey	Aberdeen Asset Management	HCM	$1,000,000
27	Jeffrey	Admiral Group	FIN	$500,000
28	Lei	Babcock International	HCM	$506,000
29	Lei	Barclays	LER	$1,500,000
30	Cian	Capita	LER	$1,895,000
31	Cian	Centrica	PAY	$2,004,560

	G	H	I
25	Salesperson	Manager	Region
26	Jeffrey	Peter	EMEA
27	Lei	Fredrik	US
28	Cian	John	UK

The formula =JOIN(A25:D31,1,G25:I28,1,,TRUE), placed in cell L4, combines the employee information with the manager and region information. The Salesperson, Manager, and Region are on the left because that data range has fewer rows.

	L	M	N	O	P	Q
4	Jeffrey	Peter	EMEA	Aberdeen Management	HCM	$1,000,000
5	Jeffrey	Peter	EMEA	Admiral Group	FIN	$500,000
6	Lei	Fredrik	US	Babcock International	HCM	$506,000
7	Lei	Fredrik	US	Barclays	LER	$1,500,000
8	Cian	John	UK	Capita	LER	$1,895,000
9	Cian	John	UK	Centrica	PAY	$2,004,560

Notes

This function does an inner left join. The function considers the range with fewer rows to be the left table.

For each row in the left table, the function checks to see if there is a matching row in the right table by comparing the key values in the key columns you specified. If a match exists, the function creates a new row by joining the row from each of the tables with the key value only appearing one time. A row appears in the output only if the join condition is satisfied. It is also possible for a row from the left table to appear multiple times if multiple rows in the right table have a matching key value.

If you want all rows from the

primary_table

to always be present in the output, use the MERGEROWS function instead.

When comparing a single instance value to a string that visually appears to be the same, the function evaluates them as equal. When comparing a single value in a multi-instance field to a string that visually appears to be the same, the formula evaluates them as not
equal.

This function is intended for use in array formulas.

Related Functions

CORRELATE