Excel is a powerful tool that allows you to organize, analyze, and manipulate data easily. While VLOOKUP is a popular function for finding a specific value in a range of cells, INDEX and MATCH can be used together to perform a more flexible two-way lookup. In this step-by-step guide, we’ll show you how to use the INDEX and MATCH functions to perform a two-way lookup in Excel.
Step 1: Open Excel and Import Data
To start, open a new Excel sheet and import the necessary data. For this example, we’ll use a Simple Table with two columns: Name and Product. The rows will contain the corresponding values for each cell.
Step 2: Define the Data Range
Next, select the range of cells you want to search within. In our example, we’ll select the entire range from A1 to B6.
Step 3: Define the Lookup Values
Now, define the two lookup values you want to use. In this example, we will search for the product associated with the name “John Doe” and return the corresponding value. For the first lookup value, select cell A7 and enter the name “John Doe“. For the second lookup value, select cell B7 and enter the product “Shirt“.
Step 4: Use INDEX and MATCH
In cell C7, enter the formula:
=INDEX(B2:B6,MATCH(A7,A2:A6,0),0)Explanation of formula:
INDEX(B2:B6: The range to return a value fromMATCH(A7,A2:A6,0)The match criteria – search for the value in the range A2:A6, and locate the position of the match.- The
,0)determines the exact match. So It returns an error if there is no exact match.
When entered correctly, Excel will return the product associated with the name “John Doe” which is “Shirt“.
Step 5: Test Other Scenarios
You can also test other scenarios with this formula, such as searching for a name associated with a product. Simply change the lookup values in cells A7 and B7 to match the desired scenario.
With this step-by-step guide, you now know how to use the INDEX and MATCH functions to perform a two-way lookup in Excel. This powerful tool allows you to efficiently search and manage data in any size range of cells.
Shop now on our website for the best prices on Microsoft Office and boost your productivity today!