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:
