5 Ways To Vlookup In Excel With Two Columns

5 Ways To Vlookup In Excel With Two Columns

Microsoft Excel's VLOOKUP function is a powerful tool for finding and retrieving data from a table or range. While it's often used to search for a value in a single column, there are situations where you need to VLOOKUP with two columns. In this article, we'll explore five ways to achieve this, including using the VLOOKUP function, INDEX-MATCH, XLOOKUP, and more.

The VLOOKUP function is often used to find a value in a table and return a corresponding value from another column. However, when working with two columns, the task becomes more complex. You might need to find a value that matches both columns or retrieve a value based on conditions in two columns.

Understanding the Limitations of VLOOKUP

Before diving into the solutions, it's essential to understand the limitations of the VLOOKUP function. By default, VLOOKUP searches for a value in the first column of a table and returns a corresponding value from another column. However, when working with two columns, you need to find a way to combine the values or use alternative functions.

Method 1: Using the VLOOKUP Function with Concatenation

One way to VLOOKUP with two columns is to concatenate the values in the two columns and then use the VLOOKUP function. This method involves creating a new column that combines the values from the two columns using the ampersand (&) symbol.

For example, suppose you have a table with the following columns:

First Name	Last Name	Age
John	Smith	30
Jane	Doe	25
Bob	Johnson	40

You can create a new column that combines the first and last names using the formula:

=First Name & " " & Last Name

This will create a new column with the combined names:

First Name	Last Name	Age	Full Name
John	Smith	30	John Smith
Jane	Doe	25	Jane Doe
Bob	Johnson	40	Bob Johnson

You can then use the VLOOKUP function to search for a value in the Full Name column and return the corresponding age.

Using the INDEX-MATCH Function

Another way to VLOOKUP with two columns is to use the INDEX-MATCH function. This method involves using the MATCH function to find the relative position of a value in a range and then using the INDEX function to return the corresponding value.

For example, suppose you have a table with the following columns:

First Name	Last Name	Age
John	Smith	30
Jane	Doe	25
Bob	Johnson	40

You can use the following formula to find the age of a person with a specific first and last name:

=INDEX(Age, MATCH(1, (First Name = "John") * (Last Name = "Smith"), 0))

This formula uses the MATCH function to find the relative position of the value "John" in the First Name column and the value "Smith" in the Last Name column. The INDEX function then returns the corresponding age.

Method 3: Using the XLOOKUP Function

The XLOOKUP function is a new function in Excel that allows you to search for a value in a table and return a corresponding value from another column. One of the advantages of the XLOOKUP function is that it can handle multiple criteria, making it ideal for VLOOKUP with two columns.

For example, suppose you have a table with the following columns:

First Name	Last Name	Age
John	Smith	30
Jane	Doe	25
Bob	Johnson	40

You can use the following formula to find the age of a person with a specific first and last name:

=XLOOKUP(1, (First Name = "John") * (Last Name = "Smith"), Age)

This formula uses the XLOOKUP function to search for the value "John" in the First Name column and the value "Smith" in the Last Name column. The function then returns the corresponding age.

Using the FILTER Function

Another way to VLOOKUP with two columns is to use the FILTER function. This method involves using the FILTER function to filter a range based on multiple criteria and then returning the corresponding value.

For example, suppose you have a table with the following columns:

First Name	Last Name	Age
John	Smith	30
Jane	Doe	25
Bob	Johnson	40

You can use the following formula to find the age of a person with a specific first and last name:

=FILTER(Age, (First Name = "John") * (Last Name = "Smith"))

This formula uses the FILTER function to filter the Age column based on the values "John" and "Smith" in the First Name and Last Name columns. The function then returns the corresponding age.

Method 5: Using Power Query

Power Query is a powerful tool in Excel that allows you to create and edit queries. One of the advantages of Power Query is that it can handle multiple criteria, making it ideal for VLOOKUP with two columns.

For example, suppose you have a table with the following columns:

First Name	Last Name	Age
John	Smith	30
Jane	Doe	25
Bob	Johnson	40

You can use the following steps to create a query that finds the age of a person with a specific first and last name:

1. Go to the Data tab in the ribbon.
2. Click on the "From Table/Range" button.
3. Select the table range.
4. Click on the "Filter" button.
5. Add a new filter for the First Name column.
6. Add a new filter for the Last Name column.
7. Click on the "OK" button.
8. Go to the "Add Column" tab.
9. Click on the "Custom Column" button.
10. Enter the formula: = Age

This will create a new query that filters the Age column based on the values "John" and "Smith" in the First Name and Last Name columns. The query then returns the corresponding age.

Vlookup Excel Columns Image Gallery

We hope this article has helped you understand the different ways to VLOOKUP with two columns in Excel. Whether you use the VLOOKUP function, INDEX-MATCH, XLOOKUP, FILTER, or Power Query, you can achieve your desired results. If you have any questions or need further assistance, feel free to ask in the comments section below.