Picking one item from a table using INTERSECT

The INTERSECT function lets you pick one item from a 2 dimensional table (rows and columns). It works much like INDEX(..MATCH…MATCH) in Excel.

The syntax is INTERSECT(<name of item that is a table>, <value to look up down the rows>, <value to look up across columns>)

For example, suppose you have an item which is a table showing the payments made by three people in each month. Call it “Monthly payments”. The people are called Alice, Bob and Charlie.  The item has two arrays: people (containing Alice, Bob and Charlie) and month( January to December).

Now suppose you have an item with the month you want – “Selected month”, with the value “Mar”.

To get the payment that Bob made in March, type INTERSECT([Monthly payments], “Bob”, [Selected month])

Note that the table must be on a table sheet, and you must have “Don’t include tables as ingredients in blocks” selected

See Add an input which is a table for more details.

An example is here: intersect example