Two-way lookups

How INDEX and MATCH and XLOOKUP works

• What is a two-way lookup?

  Finding the value where a row and a column cross: the sales for South in March, or the price for a size and a colour. You look up two things at once, one down the side and one across the top.

• How does INDEX do it?

  INDEX can take a row number and a column number: =INDEX($B$2:$G$6,3,2) gives the cell 3 rows down and 2 across. Find each number with MATCH:

  =INDEX($B$2:$G$6,MATCH(A9,$A$2:$A$6,0),MATCH(B9,$B$1:$G$1,0))

  • The first MATCH finds the region's row in the labels down the side.
  • The second finds the month's column in the labels across the top.

• What has to line up?

  The labels down the side must cover the same rows as the INDEX range, and the labels across the top the same columns. Here A2:A6 matches rows 2 to 6 and B1:G1 matches columns B to G.

• Is there an XLOOKUP way?

  Yes, one XLOOKUP inside another. The inner one returns the region's whole row; the outer one picks the month from it:

  =XLOOKUP(B9,$B$1:$G$1,XLOOKUP(A9,$A$2:$A$6,$B$2:$G$6))