Explanation

At the core, this is a normal INDEX and MATCH function :

=INDEX(array,MATCH(value,range,0))

Where the MATCH function is used to find the correct row to return from array, and the INDEX function returns the value at that array.

However, in this case we want to make the array variable, so that the range given to INDEX can be changed on the fly. We do this with the CHOOSE function:

CHOOSE(H5,Table1,Table2)

The CHOOSE function returns a value from a list using a given position or index. The value can be a constant, a cell reference, an array , or a range. In the example, the numeric index is provided in column H. When the index number is 1, we use Table1. When the index is 2, we feed Table2 to INDEX:

CHOOSE(1,Table1,Table2) // returns Table1
CHOOSE(2,Table1,Table2) // returns Table2

Note: the ranges provided to CHOOSE don’t need to be tables , or named ranges .

In I5, the number in column H is 1, so CHOOSE returns Table1, and the formula resolves to:

=INDEX(Table1,MATCH("A",Table1[Model],0),2)

The MATCH function returns the position of “A” in Table1, which is 1, and INDEX returns the value at row 1, column 2 of Table1, which is $20.00

=INDEX(Table1,1,2) // returns $20.00

Explanation

This formula pulls the customer name and state from the customer table into the order table. The MATCH function is used to locate the right customer and the INDEX function is used to retrieve the data.

Retrieving customer name

Working from the inside out, the MATCH function is used to get a row number like this:

MATCH($C5,ids,0)
  • The lookup value comes the customer id in C5, which is a mixed reference, with the column locked, so the formula can be easily copied.
  • The lookup array is the named range ids (H5:H8), the first column in the customer table.
  • The match type is set to zero to force an exact match.

The MATCH function returns 2 in this case, which goes into INDEX as the row number:

=INDEX(data,2,2)

With the column number hard-coded as 2 (customer names are in column 2) and the array set to the named range “data” (H5:J8) INDEX returns: Amy Chang.

Retrieving customer state

The formula to retrieve customer state is almost identical. The only difference is the column number is hard-coded as 3, since state info appears in the 3rd column:

=INDEX(data,MATCH($C5,ids,0),2) // get name
=INDEX(data,MATCH($C5,ids,0),3) // get state

Dynamic two-way match

By adding another MATCH function to the formula, you can set up a dynamic two-way match. For example, with the named range “headers” for H4:J4, you can use a formula like this:

=INDEX(data,MATCH($C5,ids,0),MATCH(E$4,headers,0))

Here, a second MATCH function has been added to get the correct column number. MATCH uses the current column header in the first table to locate the correct column number in the second table, and automatically returns this number to INDEX.