Two-way lookup in Excel 2013 using INDEX and MATCH

Two-way lookup gives value of the intersection of desired row and column values in a table containing data.

For example, suppose we have data as per table below, and we are looking for number of bank transactions of David in the month of August. This requirement is appropriate to go for 2 way lookup.

i.e. We are looking up value at intersection of desired customer name row (David) and desired month column (August)

To lookup the desired name of customer (e.g. David), we will use MATCH function. This function returns the relative position of particular value in the given cell range or an array.

Syntax for MATCH function:

MATCH (lookup_value, lookup_array, [match_type]

lookup_value: the value whose relative position is required

lookup_range: the range in which the relative position is to be found

[match_type]: put "0" for exact match

Thus, we will get relative row number of the customer by using MATCH function.

Similarly, to get relative column position of desired month we will use MATCH function.

Now, we have relative position of both row as well as column in the data table. To lookup the output value we will now use INDEX function.

Remember, the INDEX function gives ouput corresponding to given relative row number and column number in the data table.

Syntax for INDEX function:

INDEX (array, [row_num], [col_num])

array: the defined range of cells or an array to use in the lookup (basically the table with data)

[row_num]: relative row number in the defined range of cells

[col_num]: relative column number in the defined range of cells

Please note that "array" would not contain the row and column headings.

Thus, now the story is pretty clear. We need to feed the MATCH function used to fetch relative row number in place of [row_num] in INDEX function. Similarly, feed the MATCH function for relative column number in [col_num].

The final formula will look something like this: