How to Perform VLOOKUP with Two Lookup Values in Excel

This guide will discuss how to perform `VLOOKUP` with two lookup values in Excel.

The rules for using the `VLOOKUP` function in Excel are the following:

• The `VLOOKUP` function is used to look for a value in the leftmost column of a data set. Then, the function will return a value in the same row from the specified column.
• By default, the function will perform an approximate match.
• The function is not case-sensitive.
• The range_lookup argument will decide the match mode. If we want an exact match, we will input FALSE. Otherwise, or TRUE, the default is an approximate match.
• When the range_lookup is TRUE, 1, or omitted, the function will match the nearest value less than the lookup_value and will still use an exact match.
• When the range_lookup is FALSE or 0, the function performs an exact match and column 1 of the table_array does not have to be sorted.

Excel is an excellent tool to use for complex calculations and situations. Since it has several built-in functions and tools, we can easily manipulate our data set and perform difficult tasks.

For instance, we want to look up certain values in Excel. And we can easily do this using different functions, specifically the `VLOOKUP` function. However, there are times when we need to use more than one lookup value in the `VLOOKUP` function.

But, the `VLOOKUP` function does not support using multiple lookup values. Luckily, there are two simple ways we can go about this issue. Essentially, we will use a helper column and combine the two lookup values using the ampersand and the `CONCAT` function.

Let’s take a sample scenario wherein we must perform `VLOOKUP` with two lookup values in Excel.

Suppose we have an employee directory. And we want to extract the department for certain employees. However, the first name and last name of the employees are separated into two different columns. Thus, we need to use two lookup values.

To do this, we can create a helper column combining the first name and last name of the employees in one column. Then, we can use the `VLOOKUP` function and concatenate the two lookup values with the ampersand symbol (&).

Before we move on to a real example of performing `VLOOKUP` with two lookup values in Excel, let’s first learn the syntax of the functions we will be using.

The Anatomy of the VLOOKUP Function

The syntax or the way we write the `VLOOKUP` function is as follows:

`=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`

Let’s take apart this formula and understand what each term means:

• = the equal sign is how we activate any function in Excel.
• VLOOKUP() refers to our `VLOOKUP` function. And this function is used to return a value from the same row of the lookup value from a column we specify.
• lookup_value is a required argument. So this refers to the value found in the data set’s first column. Additionally, this value can be a value, a cell reference, or a text string.
• table_array is another required argument. And this refers to a table of text, numbers, or logical values in which data is retrieved.
• col_index_num is also a required argument that refers to the column number in the table_array from which the matching value should be returned.
• range_lookup is an optional argument. And this is a logical value that defines the type of match to look for. If we want the exact match, we input FALSE. If we want the closest match, we input TRUE.

The Anatomy of the CONCAT Function

The syntax or the way we write the `CONCAT` function is as follows:

`=CONCAT(text1, …)`

Let’s take apart this formula and understand what each term means:

• = the equal sign is how we start any function in Excel.
• CONCAT() is our `CONCAT` function. And this function is used to concatenate a list or range of text strings.
• text1 is the only required argument. So this refers to 1 to 254 text strings or ranges we want to join to a single text string.

Great! Now we can dive into a real example of performing `VLOOKUP` with two lookup values in Excel.

A Real Example of Performing VLOOKUP with Two Lookup Values in Excel

Let’s say we have a data set containing the employees’ names and their departments. And each employee separates the first name and last name into two columns. So our initial data set would look like this:

Our goal is to return the department a specific employee belongs in using their complete name as the lookup value. However, the first name and last name are separated into two columns. Thus, we have to use two lookup values when we look for the department they belong in.

In this case, we will use the `VLOOKUP` function to look for the employee name and return the department of the matching value. Since the `VLOOKUP` function does not support two lookup values, we would have to concatenate the two lookup values into one. And there are two methods we can perform.

Firstly, we will create a helper column. In this helper column, we will combine the first and last names into one column. Then, we will use the `VLOOKUP` function and the ampersand (&) to combine the two lookup values in the formula.

So the formula would utilize the helper column to find a match and successfully return the matching department.

Secondly, we will still use the helper column. However, this time we will use the `CONCAT` function together with the `VLOOKUP` function. So the `CONCAT` function will combine the two lookup values in the formula and look for a match in the helper column. Then, it will return to the matching department.

So our final data set would look like this:

You can make your own copy of the spreadsheet above using the link attached below.

Amazing! Now we can proceed to discuss the steps of how to perform `VLOOKUP` with two lookup values in Excel.

How to Perform VLOOKUP with Two Lookup Values in Excel

In this section, we will discuss the step-by-step process of how to perform `VLOOKUP` with two lookup values in Excel.

1. Firstly, we need to create a helper column. To do this, we will insert a new column on the leftmost side of the data set. Next, we will type in the formula “=B2&C2” to combine the two lookup values.

2. Next, we will drag the Fill Handle tool to copy the formula to the rest of the cells.

3. Then, we can create an area to input the lookup values and return the result. And now we can try our first method using the ampersand (&) symbol. To do this, we will input the formula “=VLOOKUP(G2&G3, A1:D10, 4, FALSE)”. Lastly, we will press the Enter key to return the result.

4. Afterward, let’s try our second method using the `CONCAT` function. To do this, we will type in the formula “=VLOOKUP(CONCAT(G2, G3), A2:D10, 4, FALSE)”. Lastly, we will press the Enter key to return the result.

5. And tada! We have successfully performed `VLOOKUP` with two lookup values in Excel.

And that’s pretty much it! We have discussed how to perform `VLOOKUP` with two lookup values in Excel using two easy and simple methods. Now you can choose one and apply it to your work.

Are you interested in learning more about what Excel can do? You can now use the `VLOOKUP` function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

Read More

How to Find and Replace Characters with a Superscript in Excel

The Find and Replace function can be used when you need to find and replace a character with…
Read More

How to Ignore #DIV/0! Values in Excel

This guide will explain how to ignore #DIV/0! values in Excel using the IFERROR function.  The rules for…
Read More

How to Perform Cluster Sampling in Excel

This guide will explain how to perform cluster sampling in Excel.  Excel is an excellent tool to use…
Read More

How to Make an ANOVA Table in Excel

This guide will explain how to make an ANOVA table in Excel. ANOVA tables allow users to test…
Read More

How to Rank a League Table in Excel

This guide will explain how you can rank teams in a sports championship with a league table in…
Read More

How to Ignore #N/A Values in Excel

This guide will discuss how to ignore #N/A values in Excel using the IFNA function.  The rules for…