Excel Formula: How to Use VLOOKUP With Multiple Criteria

Are you looking to use the VLOOKUP function in Microsoft Excel to look up values based on multiple criteria? In this comprehensive guide, we’ll show you several methods to use VLOOKUP with multiple criteria to quickly find the exact data you need in your spreadsheets, no matter how complex.

What is VLOOKUP in Excel?

Before diving into multiple criteria, let’s review the basics. VLOOKUP, short for “vertical lookup”, is one of the most powerful and widely used functions in Microsoft Excel. It allows you to efficiently look up and retrieve data from a specific column in a table based on a single lookup value.

VLOOKUP searches for the lookup value vertically in the first column of the specified table array and returns the corresponding value from a column you specify using a column index number.

The basic syntax for the VLOOKUP function is:

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

• lookup_value: The value you want to look up, which must be located in the first column of the table_array.
• table_array: The table or range that contains the data you want to retrieve.
• col_index_num: The column number in the table_array from which the matching value should be returned.
• [range_lookup]: Optional. Enter FALSE to find an exact match or TRUE (or leave blank) to find an approximate match.

Using VLOOKUP With a Single Criteria

In many cases, you’ll use VLOOKUP with just a single criteria. To do this, you simply provide the lookup value, table array, and column index number.

Here’s a straightforward example:

To look up Jane Doe’s salary, you would use the following formula:

=VLOOKUP(“Jane Doe”, A2:C4, 3, FALSE)

This formula searches for the text string “Jane Doe” in the first column of the range A2:C4, and returns the value from the 3rd column (Salary) in the same row. The result is $60,000.

Using VLOOKUP With Multiple Criteria

But what if you need to look up data based on multiple criteria, such as both an employee’s name and department? The standard VLOOKUP function alone can’t handle this directly. However, there are several ways to extend or combine VLOOKUP with other functions to enable lookups based on multiple conditions.

Let’s explore a few of the most effective methods:

1. Using VLOOKUP with Concatenation

One straightforward approach is to concatenate (combine) the multiple criteria into a single lookup value. You essentially create a new column that merges the info you want to match. Here’s an example:

Now you can VLOOKUP using the combined “Lookup Value” column:

=VLOOKUP(“John Smith – Sales”, A2:D4, 4, FALSE)

This formula will match on the concatenated name and department, and return John Smith’s $50,000 salary.

The main drawback of this method is that you need to create and populate that extra “Lookup Value” column, which can be tedious for large datasets. But it’s a good option if you have a relatively small, static table.

2. Using VLOOKUP with Helper Columns

Another option is to use one or more “helper columns” to extract the data you need for the VLOOKUP. This is useful when your criteria data is in a different table or format than your output data.

Say you have Employee data in columns A-C and a separate Department table in columns E-F. You want to look up salaries by employee name and department name.

In D2 enter this formula and drag it down:

=A2&” – “&VLOOKUP(B2, $E$2:$F$10, 2, FALSE)

This looks up the Department Name from the Department ID in B2, and concatenates it with the employee name in A2. You end up with a helper column D showing “Name – Department”.

Then do the VLOOKUP on this helper column:

=VLOOKUP(D2, A2:C10, 3, FALSE)

The helper column lets you VLOOKUP the concatenated name and department without needing that data in your main table. This keeps your table clean but can get cumbersome with very large datasets.

3. Using VLOOKUP with INDEX and MATCH

For a more dynamic, formula-based approach, you can combine VLOOKUP with the INDEX and MATCH functions. This method avoids extra columns and is very flexible.

Assume your main employee table is in A1:D10, and you want to look up the salary based on name in A1 and department in C1.

Here’s the formula:

=VLOOKUP(A1, A1:D10, MATCH(1, (A1=A1:A10)*(C1=C1:C10),0))

Here’s a breakdown of how this works:

1. The MATCH function looks in A1:A10 for the name in A1 and in C1:C10 for the department in C1.
2. The *(A1=A1:A10)*(C1=C1:C10)* part returns an array of 1s and 0s. It will be 1 only when both criteria match.
3. MATCH then returns the position of the first 1 in this array, which corresponds to the row where both criteria are met.
4. VLOOKUP then looks up this row number and returns the value from the last column, which is the salary.

This method is very powerful and avoids extra columns. The main challenge is that the formula is more complex to understand and modify. But once you grasp how it works, it’s an excellent solution for dynamic, multi-criteria lookups.

4. Using VLOOKUP with CHOOSE and IF

Another advanced way to perform a VLOOKUP with multiple criteria is by leveraging the CHOOSE and IF functions. This allows you to create a dynamic virtual table to look up against.

Assume your data is set up as in the previous examples. Here’s the formula:

=VLOOKUP(A1, CHOOSE({1,2}, IF(A1:A10=A1, IF(C1:C10=C1, B1:B10)), D1:D10), 2, 0)

Here’s how this complex formula works:

1. The CHOOSE function uses an array {1,2} to create a virtual 2-column range.
2. The IF statements check each row of the data. If column A matches the name in A1 and column C matches the department in C1, it returns the value from column B.
3. Column D is returned as the second column in the virtual range.
4. VLOOKUP then looks in this virtual range for the value in A1 and returns the corresponding value from the second column (which is column D in the real data).

This method is very flexible but can be difficult to set up and debug. It’s most useful when you need to create a virtual lookup table that doesn’t exist in your actual spreadsheet.

Which Multiple Criteria VLOOKUP Method Should You Use?

With all these options, you might be wondering which method is the best for using VLOOKUP with multiple criteria. The truth is, the best method depends on your specific data setup and needs.

Here are some general guidelines:

• Concatenation is straightforward but requires creating extra data in your sheet. It’s good for simple, one-off lookups with small datasets.
• Helper columns avoid extra data in your main table but can get complex with lots of lookups. They’re good for larger, relatively fixed datasets.
• INDEX/MATCH is very powerful and flexible, but has a steeper learning curve. It’s great for complex, dynamic datasets where you need to avoid extra columns.
• CHOOSE/IF is a creative approach, but can quickly become difficult to understand and maintain. It’s most useful for creating virtual lookup tables.

The best way to find what works for you is to experiment with these methods on your actual data. As you gain comfort with the underlying functions, you’ll be able to adapt them more easily to novel situations.

Tips for Troubleshooting VLOOKUP With Multiple Criteria

Even with a solid grasp of the techniques, you might run into issues getting your VLOOKUP formulas to return the expected results. Here are some common problems and tips to resolve them:

• Double-check that your lookup and source data are the same data type. Numbers stored as text can prevent matches.
• Look for inconsistent spacing, capitalization, or punctuation that can throw off matches. Use TRIM, LOWER, or UPPER to clean your data.
• Make sure you’re using absolute references (with $) for table ranges that shouldn’t shift when you copy formulas.
• In some setups, 0 and FALSE are interchangeable. Try both if you’re using one as a final argument.
• If you get a #N/A error, it usually means no match was found. Check your criteria and source data carefully.

Debugging complex formulas can be challenging. Try walking through the evaluation step-by-step using the Formulas tab in Excel, or break the formula into smaller parts to isolate the issue. Online forums like Stack Overflow are also great resources for specific problems.

In Summary

Using VLOOKUP with multiple criteria in Excel is a crucial skill for working with complex datasets. While the basic VLOOKUP function only works with a single criterion, you can use clever combinations of functions like CONCATENATE, INDEX, MATCH, CHOOSE, and IF to look up data based on multiple conditions.

The key is understanding how these functions work and adapting them to your specific data structure and needs. With practice, you’ll be able to create dynamic, multi-criteria lookups that make it easy to find insights in even the largest, most complex spreadsheets.

We hope this in-depth guide gives you the tools and understanding you need to confidently use VLOOKUP with multiple criteria in your own Excel projects. Keep exploring and refining these techniques, and don’t forget to check out our other Excel resources for more tips and tutorials.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.