Exploring the intricacies of conducting a Two-Way Lookup in Excel.
Imagine the need to extract a value from a table without directly referencing the table itself. This calls for a robust formula capable of identifying precise matches. A 2D lookup in Excel table requires matching both row and column indexes. For instance, locating an employee’s salary (Emp 052). Here, the match function operates twice: once for the Employee ID and again for the Salary under Columns.
Two-Way Lookup in Excel: A Guide
Solution:
To grasp the formula’s mechanics, let’s briefly revisit these crucial functions:
INDEX function
MATCH function
Formula:
Combining these functions, the MATCH function locates the index of the lookup value1 in the row header field. Similarly, another MATCH function identifies the index of the lookup in Excel value2 in the column header field. These index numbers are then employed in the INDEX function to extract the values corresponding to the lookup data.
Generic Formula:
= INDEX ( data , MATCH ( lookup_value1, row_headers, 0 , MATCH ( lookup_value2, column_headers, 0 ) ) )
Explanation:
This formula uses the lookup values to match the row_header and column_header arrays, directing INDEX to retrieve the desired data.
Example:
Let’s elucidate this with an example:
Consider a dataset displaying student scores in various subjects. The goal is to find Gary’s score in Social Studies.
The formula in cell J6:
= INDEX ( table , MATCH ( J5, row, 0 , MATCH ( J4, column, 0 ) ) )
This formula matches the Student value (J4) with the row header and Subject value (J5) with the column header, fetching Gary’s score for Social Studies (36).
Conclusion:
The formula extracts precise matches from the dataset, evident in the obtained scores. It’s notable that the MATCH function uses a type argument of 0 for exact matches. For further insights, refer to the explanatory notes below.
