Excel Multiple Criteria Lookup: The Easiest Way to Find Data with Multiple Conditions

When managing data in Excel, you will often encounter situations like these:

• Find an employee’s salary by employee name + department
• Look up sales volume by product name + date
• Find order data by customer name + region

However, standard VLOOKUP can use only one criterion, so it does not work properly in these situations.

This article explains the most efficient ways to perform an Excel multiple criteria lookup.

• Multiple-criteria XLOOKUP
• Multiple-criteria INDEX + MATCH
• FILTER function

Once you understand these methods, you can solve nearly any data lookup problem in Excel.

Quick Fix: The Easiest Solution

If you use a recent version of Excel, the following formula is the easiest option:

=XLOOKUP(1,(A2:A10=H1)*(B2:B10=H2),C2:C10)

This formula finds the row where both criteria are met and returns the corresponding value.

• Column A = first criterion
• Column B = second criterion
• Column C = return value

Sample Data (Real-World Scenario)

Employee	Department	Salary
Kim	Sales	4000
Lee	HR	3500
Kim	HR	3800
Park	Sales	4200

Values to find

Employee	Department
Kim	HR

Result

3800

Look Up Multiple Criteria with XLOOKUP

This is the simplest method for performing a multiple criteria lookup in Excel.

=XLOOKUP(1,(A2:A5=H1)*(B2:B5=H2),C2:C5)

How the Formula Works

Excel performs the following array calculations:

(A2:A5=H1)
TRUE FALSE TRUE FALSE

(B2:B5=H2)
FALSE TRUE TRUE FALSE

When you multiply the two arrays:

0 0 1 0

XLOOKUP finds the position with a value of 1 and returns the result.

Advantages

• The formula is very simple
• Fast performance
• Available in recent versions of Excel

Multiple-Criteria Lookup with INDEX MATCH

In older versions of Excel, use the INDEX + MATCH combination.

=INDEX(C2:C5,MATCH(1,(A2:A5=H1)*(B2:B5=H2),0))

Formula Explanation

The MATCH function creates a criteria array and finds the position of 1.

(A2:A5=H1)*(B2:B5=H2)

Result:

0 0 1 0

INDEX then uses this position to return the value.

Find Multiple Results with the FILTER Function

If multiple results match the criteria, the FILTER function is the best option.

=FILTER(A2:C5,(A2:A5=H1)*(B2:B5=H2))

Result:

Employee	Department	Salary
Kim	HR	3800

Real-World Example

Product	Date	Sales Volume
Apple	1/1	50
Apple	1/2	70
Banana	1/1	40

Lookup criteria

• Product = Apple
• Date = 1/2

=XLOOKUP(1,(A2:A4=H1)*(B2:B4=H2),C2:C4)

Result

70

Alternative Method for Beginners (Helper Column)

If you are new to Excel, the Helper Column method may be easier.

Create a new column and combine the two criteria.

=A2&B2

Example:

Employee	Department	Key
Kim	Sales	KimSales

Now use VLOOKUP:

=VLOOKUP(H1&H2,C2:D5,2,FALSE)

Troubleshooting

Problem	Cause	Solution
#N/A error	Criteria do not match	Check the data
Incorrect value	Incorrect range	Correct the range
No value	Spaces in the data	Use the TRIM function

Method Comparison

Method	Difficulty	Speed	Recommendation
XLOOKUP	Easy	Fast	★★★★★
INDEX MATCH	Intermediate	Fast	★★★★
FILTER	Easy	Medium	★★★★

Checklist

• Make sure the criteria have the same data format
• Make sure there are no extra spaces
• Make sure the ranges are correct
• Make sure the array calculations work properly

Conclusion

Finding data with multiple criteria in Excel is simpler than it may seem.

In particular, the XLOOKUP + criteria array method can quickly solve most data lookup problems.

If you want to learn Excel data analysis and workflow automation, explore more Excel guides.

VLOOKUP 다중 조건 다중 결과: 실무 공식 5가지