CORRELATE

Description

Creates a new matrix by combining rows from the ranges you specify. This function is similar to a database join.

Syntax

CORRELATE(

primary_table

,

key_column

,

ignore_case

, [

join_range

], [

join_key_column

], ...)

primary_table

: The matrix whose rows are used as the origin. The function combines rows from this range with rows from the other ranges if their key values match.

key_column

: The column number in the primary-range whose value is used as the primary key value for comparison with other ranges.

ignore_case

: If the type of the key column is text, then if ignore_case is TRUE, the function ignores case when comparing with other key values. If FALSE, the function considers case in the comparisons.

join_range

: The first range to combine with a row from the primary range if the key column values are equal.

join_key_column

: The column number in the first join range whose value is used as the key value for comparison with the primary key.

Example

This is the original spreadsheet, with uncombined data 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 =CORRELATE(A25:D31,1,TRUE,G25:I28,1), placed in cell G37, combines the employee information with the manager and region information.

	G	H	I	J	K	L
37	Salesperson	Customer	Product	Revenue	Manager	Region
38	Jeffrey	Aberdeen Asset Management	HCM	$1,000,000	Peter	EMEA
39	Jeffrey	Admiral Group	FIN	$500,000	Peter	EMEA
40	Lei	Babcock International	HCM	$506,000	Fredrik	US
41	Lei	Barclays	LER	$1,500,000	Fredrik	US
42	Cian	Capita	LER	$1,895,000	John	UK
43	Cian	Centrica	PAY	$2,004,560	John	UK

Notes

The CORRELATE() function requires at least one pair of

join_range

and

join_key_column

values. To use other ranges, add each one with an argument specifying which column within that range has the role of the key for that range.

The function works like this:

The function examines each row in

primary_table

.

For each range you specified, the function examines each row in the range. The function compares the value you specified as the primary key (based on the primary key column index) from the

primary_table

with the value you specified as the key within the row in the range currently being examined.

If their values match, then the function merges all the values from the row in that range (except the key column) onto the end of the row from the primary table.

A row from the primary table appears in the output only if there was at least one correlated row from at least one of the other match ranges you specified. If you want all rows from the primary to appear, regardless, use MERGEROWS. If you want a true database inner join, use JOIN.

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

JOIN