5 Ways To Vlookup On 2 Columns In Excel

Why Vlookup on 2 Columns is Essential in Excel

When working with data in Excel, it's common to need to look up values in a table based on multiple criteria. The VLOOKUP function is a powerful tool that allows you to do just that, but it's limited to searching for a single value in a single column. What if you need to VLOOKUP on 2 columns in Excel? Fortunately, there are several ways to achieve this, and we'll explore five of them in this article.

The ability to VLOOKUP on 2 columns is essential in many data analysis tasks, such as looking up customer information based on both name and email address, or retrieving product details based on both product code and category. By learning how to VLOOKUP on 2 columns, you'll be able to work more efficiently and accurately with your data.

Method 1: Using the INDEX-MATCH Function

One of the most popular methods for VLOOKUP on 2 columns is to use the INDEX-MATCH function combination. This method is more flexible and powerful than the traditional VLOOKUP function, as it allows you to search for values in multiple columns.

To use the INDEX-MATCH function, you'll need to follow these steps:

1. Identify the range of cells that you want to search.
2. Use the MATCH function to find the relative position of the value you're looking for in the first column.
3. Use the MATCH function again to find the relative position of the value you're looking for in the second column.
4. Use the INDEX function to return the value at the intersection of the two positions.

The syntax for the INDEX-MATCH function is as follows:

=INDEX(range, MATCH(lookup_value1, column1, 0), MATCH(lookup_value2, column2, 0))

Example: Using INDEX-MATCH to VLOOKUP on 2 Columns

Suppose you have a table with customer information, and you want to look up the phone number of a customer based on both their name and email address.

Name	Email	Phone
John Smith	john.smith@example.com	123-456-7890
Jane Doe	jane.doe@example.com	987-654-3210

To use the INDEX-MATCH function, you would enter the following formula:

=INDEX(C:C, MATCH(A2, A:A, 0), MATCH(B2, B:B, 0))

Assuming the data is in columns A:C, and the lookup values are in cells A2 and B2.

Method 2: Using the VLOOKUP Function with Helper Columns

Another method for VLOOKUP on 2 columns is to use the VLOOKUP function with helper columns. This method involves creating a new column that combines the values of the two columns you want to search.

To use this method, you'll need to follow these steps:

1. Create a new column that combines the values of the two columns you want to search.
2. Use the VLOOKUP function to search for the combined value in the new column.

The syntax for the VLOOKUP function is as follows:

=VLOOKUP(combined_lookup_value, range, col_index, FALSE)

Example: Using VLOOKUP with Helper Columns to VLOOKUP on 2 Columns

Suppose you have a table with customer information, and you want to look up the phone number of a customer based on both their name and email address.

Name	Email	Phone
John Smith	john.smith@example.com	123-456-7890
Jane Doe	jane.doe@example.com	987-654-3210

To use the VLOOKUP function with helper columns, you would create a new column that combines the values of the Name and Email columns.

Name	Email	Phone	Combined
John Smith	john.smith@example.com	123-456-7890	John Smith john.smith@example.com
Jane Doe	jane.doe@example.com	987-654-3210	Jane Doe jane.doe@example.com

Then, you would enter the following formula:

=VLOOKUP(A2&" "&B2, D:C, 2, FALSE)

Assuming the data is in columns A:C, and the lookup values are in cells A2 and B2.

Method 3: Using the FILTER Function (Excel 365 and Later)

If you're using Excel 365 or later, you can use the FILTER function to VLOOKUP on 2 columns. This method is more straightforward and efficient than the previous methods.

To use the FILTER function, you'll need to follow these steps:

1. Select the range of cells that you want to filter.
2. Use the FILTER function to filter the range based on the lookup values.

The syntax for the FILTER function is as follows:

=FILTER(range, (column1 = lookup_value1) * (column2 = lookup_value2))

Example: Using FILTER to VLOOKUP on 2 Columns

Suppose you have a table with customer information, and you want to look up the phone number of a customer based on both their name and email address.

Name	Email	Phone
John Smith	john.smith@example.com	123-456-7890
Jane Doe	jane.doe@example.com	987-654-3210

To use the FILTER function, you would enter the following formula:

=FILTER(C:C, (A:A = A2) * (B:B = B2))

Assuming the data is in columns A:C, and the lookup values are in cells A2 and B2.

Method 4: Using the XLOOKUP Function (Excel 365 and Later)

Another method for VLOOKUP on 2 columns is to use the XLOOKUP function. This method is similar to the VLOOKUP function, but it allows you to search for values in multiple columns.

To use the XLOOKUP function, you'll need to follow these steps:

1. Select the range of cells that you want to search.
2. Use the XLOOKUP function to search for the lookup values in the selected range.

The syntax for the XLOOKUP function is as follows:

=XLOOKUP(lookup_value1, column1, XLOOKUP(lookup_value2, column2, range))

Example: Using XLOOKUP to VLOOKUP on 2 Columns

Suppose you have a table with customer information, and you want to look up the phone number of a customer based on both their name and email address.

Name	Email	Phone
John Smith	john.smith@example.com	123-456-7890
Jane Doe	jane.doe@example.com	987-654-3210

To use the XLOOKUP function, you would enter the following formula:

=XLOOKUP(A2, A:A, XLOOKUP(B2, B:B, C:C))

Assuming the data is in columns A:C, and the lookup values are in cells A2 and B2.

Method 5: Using Power Query

Finally, you can use Power Query to VLOOKUP on 2 columns. Power Query is a powerful data manipulation tool that allows you to perform complex data analysis tasks.

To use Power Query, you'll need to follow these steps:

1. Select the range of cells that you want to search.
2. Go to the "Data" tab in the ribbon and click on "From Table/Range".
3. Use the Power Query editor to create a new query that searches for the lookup values in the selected range.

Example: Using Power Query to VLOOKUP on 2 Columns

Suppose you have a table with customer information, and you want to look up the phone number of a customer based on both their name and email address.

Name	Email	Phone
John Smith	john.smith@example.com	123-456-7890
Jane Doe	jane.doe@example.com	987-654-3210

To use Power Query, you would create a new query that searches for the lookup values in the selected range.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FilteredRows = Table.SelectRows(Source, each ([Name] = "John Smith") and ([Email] = "john.smith@example.com")),
    Result = FilteredRows{0}[Phone]
in
    Result

Assuming the data is in a table named "Table1", and the lookup values are "John Smith" and "john.smith@example.com".

Excel VLOOKUP on 2 Columns Image Gallery

We hope this article has helped you learn how to VLOOKUP on 2 columns in Excel. Whether you're using the INDEX-MATCH function, VLOOKUP with helper columns, FILTER, XLOOKUP, or Power Query, you now have the skills to perform complex data analysis tasks with ease. Try out these methods and see which one works best for you!