How can I use VLOOKUP with multiple criteria?

There are three main methods: 1) Create a helper column that concatenates your criteria with a separator, 2) Use array formulas with INDEX-MATCH for more flexibility, or 3) Use SUMIFS for numerical results. The helper column method is most beginner-friendly and works in all Excel versions.

What's the best method for two-column lookups?

For beginners, use the helper column method as it's easiest to understand and debug. For advanced users, array formulas with INDEX-MATCH offer more flexibility and don't require extra columns. If you have Excel 365, XLOOKUP is the most powerful and user-friendly option.

Why doesn't my array formula work?

Make sure you press Ctrl+Shift+Enter after typing the formula - you should see curly braces {} around it. Also ensure your ranges are the same size, properly referenced, and that you're not mixing data types (text vs numbers).

Can I use more than two criteria?

Yes! For helper columns, just concatenate more values: =A2&"-"&B2&"-"&C2. For array formulas, add more conditions: =INDEX(E:E,MATCH(1,(A:A=F2)*(B:B=G2)*(C:C=H2),0)). SUMIFS naturally supports multiple criteria pairs.

VLOOKUP with Two Columns: Multiple Criteria Lookup

helper Column	name	department	salary
John Smith-Sales	John Smith	Sales	$65,000
Jane Doe-Marketing	Jane Doe	Marketing	$58,000
Bob Johnson-IT	Bob Johnson	IT	$72,000
John Smith-IT	John Smith	IT	$68,000
Sarah Wilson-HR	Sarah Wilson	HR	$61,000

Complete Guide

Everything you need to know about how to vlookup with two columns

Overview

Standard VLOOKUP can only search for one criteria at a time. But what if you need to find data based on multiple conditions? For example, finding an employee's salary based on both their name AND department? This comprehensive guide shows you three proven methods to perform VLOOKUP with multiple criteria, complete with step-by-step instructions and real examples.

Step-by-Step Methods

Method 1: Helper Column (Recommended for Beginners)

Create a new column that combines your criteria, then use standard VLOOKUP on this combined column. This is the most straightforward approach and works in all Excel versions.

Steps:

Insert a new column in your data table (let's call it column A)
In cell A2, enter the formula: =B2&"-"&C2 (assuming columns B and C contain your criteria)
Copy this formula down for all rows in your data table
In your lookup cell, create the search criteria: =F2&"-"&G2 (where F2 and G2 are your lookup values)
Use VLOOKUP with the combined criteria: =VLOOKUP(F2&"-"&G2, A:D, 4, FALSE)
Column 4 (D) should contain the value you want to return

Example:

Scenario: Finding employee salary by Name AND Department

Data Structure: Employee table: Helper(A), Name(B), Department(C), Salary(D)

Excel Formula

=VLOOKUP("John Smith"&"-"&"Sales", A:D, 4, FALSE)

Result: Returns $65,000 (John Smith's salary in Sales department)

Method 2: Array Formula with INDEX-MATCH

Use an array formula to search multiple columns simultaneously without helper columns. This method is more elegant but requires understanding of array formulas.

Steps:

Select the cell where you want the result
Type this formula: =INDEX(D:D,MATCH(1,(B:B=F2)*(C:C=G2),0))
Press Ctrl+Shift+Enter to make it an array formula
The formula will show curly braces {} around it when entered correctly
Replace D:D with your return column, B:B and C:C with your criteria columns

Example:

Scenario: Product price lookup by Category AND Brand

Data Structure: Product table: ID(A), Category(B), Brand(C), Price(D)

Excel Formula

{=INDEX(D:D,MATCH(1,(B:B="Electronics")*(C:C="Apple"),0))}

Result: Returns $999 (price for Electronics category, Apple brand)

Method 3: SUMIFS for Numerical Results

When your return value is numerical and you want to sum matching records, SUMIFS provides a clean alternative to VLOOKUP with multiple criteria.

Steps:

Use SUMIFS when your return value is numerical
Structure: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)
Example: =SUMIFS(D:D, B:B, F2, C:C, G2)
This sums all values that match both criteria (perfect for unique matches)
Add more criteria pairs as needed for additional conditions

Example:

Scenario: Total sales by Region AND Product

Data Structure: Sales table: Date(A), Region(B), Product(C), Sales(D)

Excel Formula

=SUMIFS(D:D, B:B, "North", C:C, "Laptops")

Result: Returns $125,000 (total laptop sales in North region)

⚠️Common Mistakes to Avoid

❌Using different separators in helper column and lookup formula (e.g., "-" vs "_")
❌Forgetting to press Ctrl+Shift+Enter for array formulas
❌Including spaces in concatenation which can cause matching issues
❌Not accounting for case sensitivity in text comparisons
❌Using relative references instead of absolute references for table ranges

✨Best Practices

Use consistent, unique separators like "-" or "_" in helper columns
Test formulas with known data before applying to large datasets
Consider XLOOKUP if you have Excel 365 or Excel 2021
Document your approach with comments for future reference
Use absolute references ($A$2:$D$100) for table ranges
Validate data types match between lookup values and table data

📖 1 min read📊 Intermediate level🎯 4 key concepts

How to VLOOKUP with Two Columns

Multiple Criteria Lookup Demo

Sample Data:

Formula:

Try it yourself:

Complete Guide

Overview

Step-by-Step Methods

Method 1: Helper Column (Recommended for Beginners)

Steps:

Example:

Method 2: Array Formula with INDEX-MATCH

Steps:

Example:

Method 3: SUMIFS for Numerical Results

Steps:

Example:

⚠️Common Mistakes to Avoid

✨Best Practices

Frequently Asked Questions

Key Takeaways

Ready to Put This Into Practice?