Excel 2019 Bible

Chapter 14: Using Formulas for Matching and Lookups

14

FIGURE 14.3

A list of stores with their city and state locations

The INDEX function returns the value from a particular row and column of a range. In this

case, we pass it our table of stores, a row argument in the form of a MATCH function, and a

column number. For the City formula, we want the first column, so the column argument is

1. For the Store formula, we want the third column, so the column argument is 3.

Unless the range you use starts in A1, the row and column will not match the row and col-

umn in the spreadsheet. They relate to the top-left cell in the range, not the spreadsheet as

a whole. A formula like =INDEX(G2:P10,2,2) would return the value in cell H3. The cell

H3 is in the second row and the second column of the range G2:P10.

The second argument of the MATCH function can only be a range that is one row tall or one column wide. If you send
it a range that’s a rectangle, MATCH returns the #N/A error.

To get the correct row, we use a MATCH function. The MATCH function returns the position

in the list in which the lookup value is found. It has three arguments.

Lookup value: The value we want to find