Vlookup Two Columns In Excel Made Easy

Vlookup Two Columns in Excel Made Easy

Vlookup is a powerful function in Excel that allows you to search for a value in a table and return a corresponding value from another column. While the standard Vlookup function only allows you to search for a value in one column, there are ways to Vlookup two columns in Excel. In this article, we will explore the different methods to Vlookup two columns in Excel and provide step-by-step instructions on how to do it.

Why Vlookup Two Columns in Excel?

Vlookup is a useful function in Excel that saves you time and effort when searching for data in a large table. By Vlookuping two columns, you can search for a value in one column and return a corresponding value from another column. This is particularly useful when working with large datasets where you need to search for multiple criteria.

Method 1: Using the Vlookup Function with Multiple Criteria

One way to Vlookup two columns in Excel is by using the Vlookup function with multiple criteria. This method involves creating a helper column that combines the values from the two columns you want to search. Here's how to do it:

Create a new column that combines the values from the two columns you want to search. You can use the ampersand (&) symbol to concatenate the values.
Use the Vlookup function to search for the combined value in the helper column.
Return the corresponding value from another column.

For example, suppose you have a table with the following columns: Employee ID, Name, and Department. You want to Vlookup the Employee ID and Name to return the corresponding Department.

Employee ID	Name	Department
101	John Smith	Sales
102	Jane Doe	Marketing
103	Bob Johnson	IT

To Vlookup the Employee ID and Name, create a new column that combines the values from the Employee ID and Name columns.

Employee ID	Name	Department	Helper Column
101	John Smith	Sales	101John Smith
102	Jane Doe	Marketing	102Jane Doe
103	Bob Johnson	IT	103Bob Johnson

Then, use the Vlookup function to search for the combined value in the helper column.

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

Assuming the data is in cells A1:E10, the formula searches for the combined value in cells A2 and B2 in the helper column and returns the corresponding value from column E.

Method 2: Using the INDEX-MATCH Function

Another way to Vlookup two columns in Excel is by using the INDEX-MATCH function. This method is more flexible than the Vlookup function and allows you to search for multiple criteria. Here's how to do it:

Use the MATCH function to search for the value in one column.
Use the MATCH function again to search for the value in the second column.
Use the INDEX function to return the corresponding value from another column.

For example, suppose you have a table with the following columns: Employee ID, Name, and Department. You want to Vlookup the Employee ID and Name to return the corresponding Department.

Employee ID	Name	Department
101	John Smith	Sales
102	Jane Doe	Marketing
103	Bob Johnson	IT

To Vlookup the Employee ID and Name, use the MATCH function to search for the value in one column.

=MATCH(A2, A:A, 0)

Assuming the data is in cells A1:E10, the formula searches for the value in cell A2 in column A and returns the relative position of the value.

Then, use the MATCH function again to search for the value in the second column.

=MATCH(B2, B:B, 0)

The formula searches for the value in cell B2 in column B and returns the relative position of the value.

Finally, use the INDEX function to return the corresponding value from another column.

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

The formula returns the corresponding value from column C.

Method 3: Using the FILTER Function

If you have Excel 365 or later, you can use the FILTER function to Vlookup two columns. The FILTER function is a powerful function that allows you to filter a range of data based on multiple criteria.

For example, suppose you have a table with the following columns: Employee ID, Name, and Department. You want to Vlookup the Employee ID and Name to return the corresponding Department.

Employee ID	Name	Department
101	John Smith	Sales
102	Jane Doe	Marketing
103	Bob Johnson	IT

To Vlookup the Employee ID and Name, use the FILTER function to filter the data based on the multiple criteria.

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

Assuming the data is in cells A1:E10, the formula filters the data in column C based on the values in cells A2 and B2.

Conclusion

Vlookuping two columns in Excel is a useful skill that can save you time and effort when working with large datasets. By using the Vlookup function with multiple criteria, the INDEX-MATCH function, or the FILTER function, you can search for multiple criteria and return corresponding values from another column. Whether you are a beginner or an advanced user, mastering the Vlookup function can help you become more productive and efficient in your work.