How to do a Two Column Lookup in Excel | INDEX MATCH Formula

by

In this tutorial, we will show you how to use the Index and Match functions together to quickly and easily search for and retrieve data from multiple columns in your Excel worksheet. This tutorial will teach you how to retrieve data by looking up from two columns.

In this example, we have a table containing a list of the roll number, first names, last names, and email addresses. We want to get the last names and the email addresses based on the roll number and the first name. To do this, we will use the INDEX and MATCH functions

Step-by-step instructions

  1. In the cell where you want the returned value to appear, type “=INDEX(“ without the quotes. This will start the INDEX function.
  2. Next, select the range of cells that contains the data you want to obtain. This will be the first argument in the INDEX function.
  3. Now, type a comma “,” and then input “MATCH(“ without the quotes. This will start the MATCH function, which will be the second argument in the INDEX function.
  4. In the MATCH function, select the first set of cells you want to match with.
  5. Next, add an ampersand ‘&’, and select the second set of cells you want to match with.
  6. Add a comma “,” to go to the next argument.
  7. Select the range of cells that will search through the first set of cells you wanted to match with in step 4.
  8. Next, add an ampersand ‘&’, and select the range of cells that will search through the first set of cells you wanted to match with in step 5.
  9. Add a comma “,” to go to the next argument.
  10. Type “0” to get the exact match of the search and close with a closed parentheses “)”.
  11. Press “Enter” to complete the function.

This tells Excel to look for the concatenation of the values in cells G4 and G5 in the concatenation of columns A2:A13 and B2:B13, and return the corresponding value from column C2:C13.

Conclusion

Using the Index and Match functions in Excel can be a powerful tool for searching and retrieving data from multiple columns. Remember to play around with the formulas and data to get more familiar with the functions and their capabilities. With the knowledge of the INDEX and MATCH functions, you’ll be able to effectively handle and retrieve data from your worksheets.

Related Posts: