VLOOKUP with Multiple Criteria in Excel: 5 Easy Ways

Posted on by

CONTENTS

VLOOKUP with Multiple Criteria in Excel: 5 Easy Ways

VLOOKUP with multiple criteria becomes necessary when one value is not enough to identify the row you need. You may need to find sales for a specific Product + Region, a salary for an Employee + Department, or inventory for an Item + Warehouse + Size. Standard VLOOKUP accepts a single lookup value, but with the right setup you can easily make it work with two, three, or even more conditions.

This guide starts with the easiest solution and then shows methods for users who cannot add a helper column, users working across different worksheets, Microsoft 365 users, and situations where several matching rows must be returned instead of only the first one.

If you only need the answer, start with the Quick Fix below. If you want to understand why the formula works and how to avoid common errors such as #N/A, duplicates, spaces, and mismatched data types, continue through the full guide.

Quick Fix: VLOOKUP with Multiple Criteria

The easiest and most reliable way to perform a VLOOKUP with multiple criteria is to combine the criteria into a helper column.

Imagine your worksheet contains these columns:

  • Column A: Helper Key
  • Column B: Product
  • Column C: Region
  • Column D: Sales Rep
  • Column E: Revenue

In cell A2, combine Product and Region:

=B2&"|"&C2

Fill the formula down the helper column.

Now suppose:

  • H2 contains Monitor
  • H3 contains West

Use this VLOOKUP formula:

=VLOOKUP(H2&"|"&H3,$A$2:$E$13,5,FALSE)

The lookup value becomes:

Monitor|West

Excel searches for that combined key in column A and returns the Revenue value from column E.

Expected result: $25,900

That is the basic solution. The rest of this article explains how to make it safer, more flexible, and suitable for real business workbooks.

Why Does VLOOKUP Need a Workaround for Multiple Conditions?

The standard VLOOKUP syntax is:

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

VLOOKUP searches for one lookup_value in the first column of the selected table and returns a value from another column in the same row.

For example:

=VLOOKUP("P1001",A2:D100,4,FALSE)

This works when P1001 uniquely identifies a record. But many real business datasets do not have a single unique lookup value.

Consider this sales data:

Product Region Revenue
Laptop East $48,500
Laptop West $52,200
Monitor East $31,100
Monitor West $25,900

If you search only for Laptop, Excel cannot know whether you mean Laptop + East or Laptop + West.

The solution is to turn two conditions into one lookup key:

Laptop|East
Laptop|West
Monitor|East
Monitor|West

Each combination can then be treated as a normal VLOOKUP value.

This is why concatenation is so useful for multiple-condition lookups.

Why You Should Add a Separator

You may see formulas such as:

=B2&C2

That often works, but using a separator is safer.

For example:

=B2&"|"&C2

Without a separator, different combinations can occasionally create the same text string.

For example:

AB + 12 = AB12
A + B12 = AB12

Those are two different combinations, but Excel sees the same lookup key.

With a separator:

AB|12
A|B12

the keys stay distinct.

Practice Data for This Tutorial

You can recreate every major example in this article with the following dataset.

Row Key Product Region Sales Rep Revenue
2Laptop|EastLaptopEastMaya48500
3Laptop|WestLaptopWestNoah52200
4Monitor|EastMonitorEastLiam31100
5Keyboard|SouthKeyboardSouthEmma18400
6Laptop|EastLaptopEastOlivia27600
7Monitor|WestMonitorWestEthan25900
8Keyboard|EastKeyboardEastAva22100
9Mouse|NorthMouseNorthMason14600
10Laptop|SouthLaptopSouthSophia41300
11Mouse|EastMouseEastLucas16500
12Monitor|SouthMonitorSouthMia33800
13Keyboard|WestKeyboardWestJames19700

Put the following headers in row 1:

A1: Key
B1: Product
C1: Region
D1: Sales Rep
E1: Revenue

Then enter this formula in A2:

=B2&"|"&C2

Double-click the fill handle or drag the formula down through row 13.

Notice that Laptop + East appears twice. We intentionally included that duplicate because it demonstrates one of the most important limitations of VLOOKUP later in this article.

Method 1: VLOOKUP with a Helper Column

For most beginners and for workbooks that must remain easy for coworkers to audit, a helper column is usually the clearest VLOOKUP solution.

Step 1: Create the Combined Key

In A2:

=B2&"|"&C2

The result for the first row is:

Laptop|East

Step 2: Create Lookup Input Cells

Use:

H2: Monitor
H3: West

Step 3: Write the VLOOKUP Formula

=VLOOKUP(H2&"|"&H3,$A$2:$E$13,5,FALSE)

The formula performs four operations:

  1. H2&"|"&H3 creates Monitor|West.
  2. VLOOKUP searches column A for that combined key.
  3. The table array spans columns A through E.
  4. The number 5 tells VLOOKUP to return the fifth column, Revenue.

The final FALSE means exact match.

For ordinary business identifiers, names, SKUs, regions, departments, and combinations of text criteria, exact matching is normally what you want.

Why the Dollar Signs Matter

Notice that the lookup table uses absolute references:

$A$2:$E$13

If you copy the formula downward, Excel keeps the lookup range fixed.

Without the dollar signs:

A2:E13

the next copied formula could become:

A3:E14

and eventually exclude records that should still be part of your lookup table.

A Better Long-Term Setup: Convert the Data to an Excel Table

Select the dataset and press:

Ctrl + T

Confirm that My table has headers is selected.

An Excel Table automatically expands when new records are added, which can make formulas easier to maintain than fixed ranges.

If you want a broader comparison of Excel lookup approaches, see how to find and return values with VLOOKUP, XLOOKUP, INDEX/MATCH, FILTER, and OFFSET.

Method 2: VLOOKUP Multiple Criteria Without a Helper Column

Sometimes you cannot change the source data. Perhaps the workbook is exported from an ERP system, downloaded from a customer portal, or shared with other teams that expect the columns to remain untouched.

In that situation, you can construct a temporary two-column lookup array with CHOOSE.

Using the same Product, Region, and Revenue data, enter:

=VLOOKUP(H2&"|"&H3,CHOOSE({1,2},$B$2:$B$13&"|"&$C$2:$C$13,$E$2:$E$13),2,FALSE)

How the Formula Works

The important section is:

CHOOSE({1,2},$B$2:$B$13&"|"&$C$2:$C$13,$E$2:$E$13)

Conceptually, Excel builds a temporary virtual table like this:

Virtual Column 1 Virtual Column 2
Laptop|East48500
Laptop|West52200
Monitor|East31100
Keyboard|South18400

VLOOKUP then searches the first virtual column and returns the value from virtual column 2.

Advantages

  • No permanent helper column is required.
  • The original dataset can remain unchanged.
  • You can continue using VLOOKUP if that is the lookup function your workbook already uses.

Disadvantages

  • The formula is harder for beginners to read.
  • Large array calculations can make a workbook harder to maintain.
  • Legacy Excel versions can require array-formula handling that newer Excel versions do not.

If you control the workbook structure, I generally prefer the helper-column method for a traditional VLOOKUP solution because another person can inspect the lookup key immediately.

Method 3: VLOOKUP with Three or More Criteria

The same idea works with three criteria.

Suppose Product + Region is not unique because more than one salesperson can sell the same product in the same region.

Our sample data contains:

Product Region Sales Rep Revenue
Laptop East Maya 48500
Laptop East Olivia 27600

Searching for only Laptop + East is therefore not enough.

Create a three-condition helper key:

=B2&"|"&C2&"|"&D2

The two records become:

Laptop|East|Maya
Laptop|East|Olivia

Assume:

H2 = Laptop
H3 = East
H4 = Olivia

Then:

=VLOOKUP(H2&"|"&H3&"|"&H4,$A$2:$E$13,5,FALSE)

Expected result: 27600

You can extend the same pattern to four conditions:

=A2&"|"&B2&"|"&C2&"|"&D2

or five:

=A2&"|"&B2&"|"&C2&"|"&D2&"|"&E2

However, once the key becomes this complex, it is worth asking whether VLOOKUP is still the best tool. XLOOKUP, INDEX/MATCH, FILTER, Power Query, or a proper unique ID may create a cleaner long-term design.

VLOOKUP Multiple Criteria from Another Sheet

Another common search is how to perform a VLOOKUP with multiple criteria when the source table is on a different worksheet.

Suppose your workbook contains:

  • Report — the worksheet where you need the result
  • Sales — the source database

On the Sales sheet:

A = Helper Key
B = Product
C = Region
D = Sales Rep
E = Revenue

In Sales!A2:

=B2&"|"&C2

Now go to the Report worksheet.

Suppose:

Report!B2 = Monitor
Report!C2 = West

Enter:

=VLOOKUP(B2&"|"&C2,Sales!$A$2:$E$1000,5,FALSE)

The logic is exactly the same. The only difference is that the lookup table includes the worksheet reference:

Sales!$A$2:$E$1000

What If the Sheet Name Contains Spaces?

If the sheet is named Sales Data, Excel surrounds the name with apostrophes:

=VLOOKUP(B2&"|"&C2,'Sales Data'!$A$2:$E$1000,5,FALSE)

You do not need to memorize the apostrophe rule. While building the formula, click the source sheet and select the range with your mouse; Excel will create the reference automatically.

VLOOKUP with Multiple Criteria Including a Date

Dates create additional problems because Excel stores valid dates as serial numbers while displaying them using a date format.

If one of your lookup conditions is a date, normalize the date component when creating the helper key.

Imagine:

  • Column B = Product
  • Column C = Order Date
  • Column D = Revenue

Create the key:

=B2&"|"&TEXT(C2,"yyyymmdd")

A transaction for Laptop on August 15, 2026 becomes:

Laptop|20260815

Then, if H2 contains the Product and H3 contains a valid Excel date:

=VLOOKUP(H2&"|"&TEXT(H3,"yyyymmdd"),$A$2:$D$1000,4,FALSE)

This approach makes the lookup key easier to inspect and prevents the displayed date format from being mistaken for the actual underlying value.

Important: Check Whether Your Dates Are Real Dates

A value that looks like 8/15/2026 may still be stored as text, especially when the data came from CSV files, websites, external systems, or copied reports.

If one side of the lookup contains true Excel dates and the other contains text, the values can look identical on screen but fail to match.

Method 4: XLOOKUP with Multiple Criteria

If you use a current Microsoft 365 or another supported modern Excel version, you should also consider XLOOKUP.

XLOOKUP separates the lookup array from the return array and uses exact matching by default. It can also return a custom value when no match is found and can search in either direction.

A multiple-condition XLOOKUP can be written without a helper column:

=XLOOKUP(1,($B$2:$B$13=H2)*($C$2:$C$13=H3),$E$2:$E$13,"Not found")

Why Are We Looking Up the Number 1?

Each condition creates TRUE and FALSE results.

For example:

($B$2:$B$13=H2)

tests whether each Product matches H2.

And:

($C$2:$C$13=H3)

tests whether each Region matches H3.

Multiplying the arrays works like an AND condition:

TRUE * TRUE = 1
TRUE * FALSE = 0
FALSE * TRUE = 0
FALSE * FALSE = 0

Therefore, XLOOKUP searches for 1—the row where both conditions are true.

Alternative XLOOKUP Using Concatenation

You can also write:

=XLOOKUP(H2&"|"&H3,$B$2:$B$13&"|"&$C$2:$C$13,$E$2:$E$13,"Not found")

That formula is conceptually similar to the VLOOKUP helper-key approach, except the combined lookup array is created inside the formula.

VLOOKUP vs XLOOKUP for Multiple Criteria

Feature VLOOKUP XLOOKUP
Exact match Use FALSE Default behavior
Lookup to the left Not directly Yes
Custom not-found result Usually IFERROR Built in
Multiple criteria Helper key or array workaround Boolean arrays or concatenation
Legacy compatibility Excellent Not available in older Excel releases such as Excel 2016 and Excel 2019

Microsoft itself describes XLOOKUP as a newer alternative to VLOOKUP and notes that XLOOKUP can search regardless of which side the return column is located on.

For a broader look at new lookup and dynamic-array functions, read the Excel new functions guide covering XLOOKUP, TEXTSPLIT, TAKE, DROP, GROUPBY, and more.

Method 5: INDEX MATCH with Multiple Criteria

INDEX and MATCH remain useful when you want a flexible lookup pattern or need to work in environments where XLOOKUP is unavailable.

For our Product + Region example:

=INDEX($E$2:$E$13,MATCH(1,($B$2:$B$13=H2)*($C$2:$C$13=H3),0))

Breaking It Down

This portion:

($B$2:$B$13=H2)*($C$2:$C$13=H3)

creates an array of zeros and ones.

MATCH searches for the first 1:

MATCH(1,...,0)

The final zero means exact match.

Once MATCH identifies the correct row position, INDEX returns the Revenue value from:

$E$2:$E$13

Why Use INDEX MATCH?

  • Lookup and return columns do not have to be arranged left-to-right.
  • The return range is explicitly defined.
  • It works well in many established workbooks built before XLOOKUP became widely available.
  • The pattern can be extended to multiple criteria.

Microsoft also recommends INDEX and MATCH as an alternative when VLOOKUP’s left-to-right limitation does not fit the worksheet structure.

For more lookup choices and their tradeoffs, see VLOOKUP vs XLOOKUP vs INDEX/MATCH vs FILTER.

VLOOKUP Multiple Criteria vs Multiple Results: They Are Different Problems

This distinction is extremely important.

Multiple criteria means:

Find a record using more than one condition.

Example:

Product = Laptop
Region = East

Multiple results means:

Return every record that matches the conditions.

Our practice dataset contains two Laptop + East records:

Product Region Sales Rep Revenue
Laptop East Maya 48500
Laptop East Olivia 27600

A standard VLOOKUP does not return both rows. It returns the first matching row it encounters.

If you want all matching records and your Excel version supports FILTER, use:

=FILTER($B$2:$E$13,($B$2:$B$13=H2)*($C$2:$C$13=H3),"No matches")

If:

H2 = Laptop
H3 = East

FILTER returns both matching rows.

The results spill into neighboring cells automatically.

Return Only Revenue Values

If you only want Revenue rather than the entire record:

=FILTER($E$2:$E$13,($B$2:$B$13=H2)*($C$2:$C$13=H3),"No matches")

Expected results:

48500
27600

Microsoft’s FILTER documentation specifically demonstrates multiplying Boolean conditions to filter records using multiple criteria.

This is an important reason not to force VLOOKUP to solve every lookup problem. VLOOKUP is designed to locate a matching row and return a corresponding value. FILTER is designed to return an array of rows that satisfy conditions.

Why Does VLOOKUP Return the Wrong Record When There Are Duplicates?

One of the most common complaints is:

“My VLOOKUP formula works, but it keeps returning the wrong person’s amount.”

The formula may actually be behaving correctly.

Suppose your helper key contains:

Laptop|East
Laptop|West
Monitor|East
Keyboard|South
Laptop|East

There are two Laptop|East records.

A standard exact-match VLOOKUP returns the first matching occurrence from the top.

If you intended to distinguish Maya from Olivia, Product + Region is not a unique key.

You need another condition:

Product + Region + Sales Rep

For example:

=B2&"|"&C2&"|"&D2

Now:

Laptop|East|Maya
Laptop|East|Olivia

are unique.

A Useful Rule

Before writing any lookup formula, ask:

“Do my conditions identify exactly one row?”

If the answer is no, you have three choices:

  1. Add more criteria until the lookup key is unique.
  2. Decide that you intentionally want the first matching result.
  3. Use FILTER to return all matching records.

This simple question prevents many VLOOKUP mistakes.

VLOOKUP Multiple Criteria Not Working? Troubleshooting Guide

If your formula looks correct but returns #N/A, an unexpected value, or another error, check the following issues in order.

Problem Likely Cause Fix
#N/A The combined key does not exist Compare the lookup key with the helper key
#N/A Leading or trailing spaces Use TRIM or clean the source data
#N/A Number stored as text Convert both lookup values to the same data type
Wrong result Duplicate combined keys Add another criterion or use FILTER
Wrong result Approximate match used accidentally Use FALSE for exact VLOOKUP matches
#REF! Return column number exceeds table width Correct col_index_num
Formula breaks after copying Lookup range is relative Use absolute references with $
Different-sheet lookup fails Wrong worksheet reference Check the sheet name and selected range
Date does not match Text date vs true Excel date Normalize or convert the date value

Problem 1: Hidden Spaces

These values appear identical:

Laptop
Laptop

but the second value contains a trailing space.

Excel treats them as different text values.

You can clean ordinary leading, trailing, and repeated spaces with:

=TRIM(B2)

Data copied from websites and external applications may also contain nonprinting or special space characters.

If lookup errors keep appearing after copying data from external systems, see the TRIM, CLEAN, and SUBSTITUTE data-cleaning guide.

Problem 2: Numbers Stored as Text

These values can look identical:

1001
1001

but one may be numeric and the other may be text.

Microsoft specifically warns that VLOOKUP can return unexpected results when number or date lookup values are stored as text.

Useful checks include:

  • Is the number unexpectedly left-aligned?
  • Does Excel display a green warning triangle?
  • Did the data come from a CSV, ERP export, website, or copied report?

You can convert text numbers with:

=VALUE(A2)

For more basic data-type and formula issues, see 10 common Excel beginner problems and their solutions.

Problem 3: FALSE Is Missing

This formula:

=VLOOKUP(H2&"|"&H3,$A$2:$E$13,5)

omits the fourth argument.

For ordinary exact-match multiple-condition lookups, use:

=VLOOKUP(H2&"|"&H3,$A$2:$E$13,5,FALSE)

Using FALSE explicitly makes the intent obvious and avoids accidental approximate-match behavior.

Problem 4: IFERROR Is Hiding a Real Problem

You may want a cleaner message:

=IFERROR(VLOOKUP(H2&"|"&H3,$A$2:$E$13,5,FALSE),"Not found")

This is useful in a finished report.

However, do not immediately wrap every new formula in IFERROR while debugging it. IFERROR can hide problems such as incorrect ranges or invalid column indexes.

First confirm that the formula works. Then add friendly error handling.

Use Exact Ranges Instead of Entire Columns in Complex Array Formulas

For a simple worksheet, entire-column references are convenient:

B:B
C:C
E:E

But complex formulas that construct arrays across entire columns can perform far more calculations than necessary.

Instead of:

=XLOOKUP(1,(B:B=H2)*(C:C=H3),E:E)

use a realistic range:

=XLOOKUP(1,($B$2:$B$50000=H2)*($C$2:$C$50000=H3),$E$2:$E$50000)

or convert the source range to an Excel Table so that the references grow with the dataset.

VLOOKUP vs XLOOKUP vs INDEX MATCH vs FILTER

There is no rule saying every lookup must use VLOOKUP. Choose the formula based on the result you actually need.

Situation Recommended Method Why
Simple single-condition lookup VLOOKUP or XLOOKUP Easy to understand
Two or three conditions with legacy compatibility VLOOKUP + helper column Transparent and easy to audit
Cannot modify source table XLOOKUP, INDEX/MATCH, or VLOOKUP + CHOOSE No permanent helper column required
Lookup value is to the right of return column XLOOKUP or INDEX/MATCH No left-to-right VLOOKUP restriction
Need all matching records FILTER Returns multiple rows
Need first matching record VLOOKUP or XLOOKUP Designed for a single match result
Need last matching record XLOOKUP Supports reverse search
Old workbook shared across many Excel versions VLOOKUP or INDEX/MATCH Broader legacy compatibility

My Practical Recommendation

Use this decision sequence:

  1. Do you need multiple matching rows? Use FILTER.
  2. Do you have XLOOKUP? Consider XLOOKUP for new lookup formulas.
  3. Do coworkers use older Excel versions? Use VLOOKUP or INDEX/MATCH.
  4. Do you specifically need multiple criteria with VLOOKUP? Use a helper column when possible.
  5. Can the combination occur more than once? Add another criterion or use FILTER.

The goal is not to prove that one Excel function is universally better. The goal is to choose a formula that another person can understand six months later.

Real Business Example: Product + Warehouse Lookup

Consider an inventory workbook used by an ecommerce company.

SKU Warehouse Available Qty
TS-001East840
TS-001West460
TS-002East320
TS-002West715

A lookup using only SKU TS-001 is ambiguous because that SKU exists in two warehouses.

Add a helper column:

=A2&"|"&B2

The keys become:

TS-001|East
TS-001|West
TS-002|East
TS-002|West

Assume:

F2 = TS-002
G2 = West

Then:

=VLOOKUP(F2&"|"&G2,$D$2:$E$5,2,FALSE)

where column D contains the helper key and column E contains Available Qty.

The result is:

715

This same structure is useful for:

  • SKU + Warehouse
  • SKU + Size
  • SKU + Color
  • Customer + Contract
  • Employee + Department
  • Vendor + Currency
  • Product + Country
  • Store + Date
  • Account + Cost Center

Real Business Example: Employee + Department

Suppose two employees have the same name:

Name Department Employee ID
James LeeFinanceE1042
James LeeOperationsE2187

A lookup using only James Lee is unsafe.

Create:

=A2&"|"&B2

which produces:

James Lee|Finance
James Lee|Operations

Now the combination identifies the intended record.

This is an important database principle hiding inside an Excel formula: a lookup works best when the lookup key uniquely identifies one record.

Advanced Tip: When You Should Stop Building Bigger VLOOKUP Formulas

It is possible to keep extending concatenated keys:

Product|Region|Warehouse|Month|Channel|Customer

But a formula being possible does not mean it is the best design.

If your lookup requires many fields, ask whether the source data should contain a true unique transaction ID, order ID, employee ID, SKU-location ID, or other business key.

For large recurring data transformations, Power Query may also be more appropriate because it can merge tables based on matching columns without requiring thousands of copied lookup formulas.

For dynamic range techniques and alternatives, you can also read the Excel OFFSET function guide.

VLOOKUP Multiple Criteria Checklist

Before assuming your formula is broken, check these seven points:

  1. Are all required criteria included?
  2. Does the combined key uniquely identify a row?
  3. Did you use a separator between concatenated criteria?
  4. Are text, numbers, and dates stored in compatible formats?
  5. Are there hidden spaces or nonprinting characters?
  6. Did you use FALSE for an exact VLOOKUP match?
  7. If multiple matching rows are intentional, should you be using FILTER instead?

If those seven items are correct, most multiple-criteria lookup problems become much easier to diagnose.

Frequently Asked Questions

Can VLOOKUP use multiple criteria?

Yes. VLOOKUP accepts one lookup value, so the usual technique is to combine two or more criteria into one lookup key. You can create that key in a helper column or construct a temporary lookup array inside the formula.

How do I use VLOOKUP with two conditions?

Create a helper key such as =B2&"|"&C2, then combine the two lookup cells in the same order:

=VLOOKUP(H2&"|"&H3,$A$2:$E$100,5,FALSE)

Can I use VLOOKUP with multiple criteria without a helper column?

Yes. One option is VLOOKUP with CHOOSE:

=VLOOKUP(H2&"|"&H3,CHOOSE({1,2},B2:B100&"|"&C2:C100,E2:E100),2,FALSE)

Modern Excel users can also consider XLOOKUP or FILTER depending on the required result.

Why does VLOOKUP return only one result?

VLOOKUP returns the first matching record it finds. If several rows meet your criteria and you want all of them, use FILTER in a supported Excel version or redesign the lookup so that your criteria uniquely identify one row.

Is XLOOKUP better than VLOOKUP for multiple criteria?

XLOOKUP is often simpler for new Microsoft 365 workbooks because it supports separate lookup and return arrays, exact matching by default, built-in not-found handling, and flexible search direction. VLOOKUP remains useful when compatibility with older Excel workbooks or users is important.

Why does my multiple-criteria VLOOKUP return #N/A?

The most common causes are a missing combined key, hidden spaces, text-versus-number differences, text dates versus real dates, or the criteria being concatenated in a different order between the source key and lookup formula.

Final Takeaway

The easiest way to perform a VLOOKUP with multiple criteria in Excel is still the helper-column approach:

=B2&"|"&C2

followed by:

=VLOOKUP(H2&"|"&H3,$A$2:$E$100,5,FALSE)

It is simple, visible, and easy for another Excel user to audit.

If you cannot add a helper column, VLOOKUP with CHOOSE can create a virtual lookup table. If you use a modern Excel version, XLOOKUP often provides a cleaner way to work with multiple Boolean conditions. INDEX/MATCH remains a flexible alternative, while FILTER is the correct choice when you need every matching record rather than only one.

Most importantly, check whether your criteria actually identify one unique row. Many apparent VLOOKUP errors are not formula errors at all—they are data-design problems caused by duplicate keys, hidden spaces, inconsistent formats, or incomplete conditions.

Continue Learning

Official Excel References