5 Ways To Lookup 2 Columns In Excel

Looking up data in Excel can be a daunting task, especially when dealing with large datasets. One common challenge is looking up values in two columns to find a corresponding value in another column. Fortunately, Excel provides several ways to accomplish this task. In this article, we will explore five ways to lookup 2 columns in Excel.

Understanding the Problem

When working with data in Excel, you may encounter situations where you need to find a value based on two conditions. For instance, you might have a table with employee names, department names, and corresponding salaries. To find the salary of a specific employee in a particular department, you need to lookup values in two columns: employee name and department name.

Method 1: Using the INDEX-MATCH Function

One of the most powerful ways to lookup 2 columns in Excel is by using the INDEX-MATCH function combination. This method is flexible and can be used in a variety of situations.

Here's the syntax:

=INDEX(range, MATCH(1, (column1 = value1) * (column2 = value2), 0))

Where:

• range is the range of cells that contains the value you want to return.
• column1 and column2 are the columns that you want to lookup.
• value1 and value2 are the values that you want to match.

For example, suppose you have the following data:

Employee Name	Department Name	Salary
John Smith	Sales	50000
Jane Doe	Marketing	60000
Bob Johnson	Sales	70000

To find the salary of John Smith in the Sales department, you can use the following formula:

=INDEX(C:C, MATCH(1, (A:A = "John Smith") * (B:B = "Sales"), 0))

This formula returns the value 50000, which is the salary of John Smith in the Sales department.

How it Works

The INDEX-MATCH function combination works by using the MATCH function to find the relative position of the value in the range. The MATCH function returns an array of values that indicate the position of the value in the range. The INDEX function then uses this array to return the corresponding value from the range.

Method 2: Using the VLOOKUP Function

Another way to lookup 2 columns in Excel is by using the VLOOKUP function. However, this method requires you to use a helper column to combine the values from the two columns.

Here's the syntax:

=VLOOKUP(value1 & value2, range, column_index, FALSE)

Where:

• value1 and value2 are the values that you want to match.
• range is the range of cells that contains the data.
• column_index is the column index of the value that you want to return.

For example, suppose you have the following data:

Employee Name	Department Name	Salary
John Smith	Sales	50000
Jane Doe	Marketing	60000
Bob Johnson	Sales	70000

To find the salary of John Smith in the Sales department, you can use the following formula:

=VLOOKUP("John Smith" & "Sales", A:C, 3, FALSE)

This formula returns the value 50000, which is the salary of John Smith in the Sales department.

How it Works

The VLOOKUP function works by searching for the value in the first column of the range. When it finds a match, it returns the corresponding value from the specified column index.

Method 3: Using the FILTER Function

If you have Excel 365 or later, you can use the FILTER function to lookup 2 columns.

Here's the syntax:

=FILTER(range, (column1 = value1) * (column2 = value2))

Where:

• range is the range of cells that contains the data.
• column1 and column2 are the columns that you want to lookup.
• value1 and value2 are the values that you want to match.

For example, suppose you have the following data:

Employee Name	Department Name	Salary
John Smith	Sales	50000
Jane Doe	Marketing	60000
Bob Johnson	Sales	70000

To find the salary of John Smith in the Sales department, you can use the following formula:

=FILTER(C:C, (A:A = "John Smith") * (B:B = "Sales"))

This formula returns the value 50000, which is the salary of John Smith in the Sales department.

How it Works

The FILTER function works by filtering the range based on the conditions specified. When it finds a match, it returns the corresponding value from the range.

Method 4: Using Power Query

Another way to lookup 2 columns in Excel is by using Power Query. Power Query is a powerful tool that allows you to manipulate and analyze data in Excel.

Here's the syntax:

=Table.SelectRows(range, each [Employee Name] = value1 and [Department Name] = value2)

Where:

• range is the range of cells that contains the data.
• value1 and value2 are the values that you want to match.

For example, suppose you have the following data:

Employee Name	Department Name	Salary
John Smith	Sales	50000
Jane Doe	Marketing	60000
Bob Johnson	Sales	70000

To find the salary of John Smith in the Sales department, you can use the following formula:

=Table.SelectRows(Table1, each [Employee Name] = "John Smith" and [Department Name] = "Sales")

This formula returns the value 50000, which is the salary of John Smith in the Sales department.

How it Works

Power Query works by selecting the rows that match the conditions specified. When it finds a match, it returns the corresponding value from the range.

Method 5: Using the XLOOKUP Function

If you have Excel 2019 or later, you can use the XLOOKUP function to lookup 2 columns.

Here's the syntax:

=XLOOKUP(value1 & value2, range, column_index, FALSE)

Where:

• value1 and value2 are the values that you want to match.
• range is the range of cells that contains the data.
• column_index is the column index of the value that you want to return.

For example, suppose you have the following data:

Employee Name	Department Name	Salary
John Smith	Sales	50000
Jane Doe	Marketing	60000
Bob Johnson	Sales	70000

To find the salary of John Smith in the Sales department, you can use the following formula:

=XLOOKUP("John Smith" & "Sales", A:C, 3, FALSE)

This formula returns the value 50000, which is the salary of John Smith in the Sales department.

How it Works

The XLOOKUP function works by searching for the value in the first column of the range. When it finds a match, it returns the corresponding value from the specified column index.

Lookup 2 Columns in Excel Image Gallery

In conclusion, looking up 2 columns in Excel can be a challenging task, but there are several ways to accomplish it. By using the INDEX-MATCH function combination, VLOOKUP function, FILTER function, Power Query, or XLOOKUP function, you can easily find the corresponding value from the range. We hope this article has helped you to learn more about lookup functions in Excel and how to use them to solve your data analysis problems.

Share your thoughts and experiences with lookup functions in the comments section below. Do you have any favorite lookup functions or techniques that you would like to share? Let us know!