Do I have to use COUNTIFS when creating advanced VLOOKUP formulas with multiple criteria?

You do not have to use COUNTIFS, but the COUNTIFS + VLOOKUP combination is the most intuitive approach when you want to remove duplicates or retrieve only the first match. After using COUNTIFS to indicate which occurrence each combination of criteria is, leave the key only on the first row so VLOOKUP always finds only that first row.

Can I create an advanced VLOOKUP formula with multiple criteria without a helper column?

Yes. You can use the CHOOSE function to create a two-column virtual table consisting of a combined-key array and a return array, then apply VLOOKUP without adding a helper column to the worksheet. You can also use INDEX MATCH by matching against the same combined-key array and returning the value with INDEX.

What should I do when a VLOOKUP formula with multiple criteria is too slow?

First, reduce references from entire columns, such as B:B, to the smallest range containing actual data, and eliminate unnecessary calculations. If it is still slow, use SUMIFS or SUMPRODUCT for aggregation tasks such as sums, counts, or averages; for large datasets, consider redesigning the workbook with a PivotTable or Power Pivot. If the lookup itself is complex, consider switching to XLOOKUP or INDEX MATCH.

Can I handle OR conditions, such as Seoul or Incheon, with VLOOKUP using multiple criteria?

VLOOKUP itself does not directly support OR conditions, but you can handle them by grouping conditions in a helper column in advance. For example, convert Seoul or Incheon branches to one common key, then perform VLOOKUP based on that key. However, when complex AND and OR conditions are mixed in an aggregation, SUMPRODUCT or FILTER is usually a more natural choice than VLOOKUP.

When is XLOOKUP better than VLOOKUP when multiple criteria are needed?

XLOOKUP is more advantageous than VLOOKUP when you need to look up values regardless of direction, or need advanced options such as custom error values, returning multiple result columns, or finding the last match. In particular, the pattern of creating multiple criteria by multiplying Boolean arrays and looking up 1 works well with XLOOKUP. If compatibility with older workbooks is not important, consider using XLOOKUP as the default for multiple-criteria designs and VLOOKUP as legacy support.

CONTENTS

Advanced VLOOKUP for Multiple Criteria: Remove Duplicates, OR Conditions, and Partial Matches

We have already covered VLOOKUP with multiple criteria once, but many real-world situations cannot be solved with the basics alone. This article walks through advanced VLOOKUP for multiple criteria patterns step by step, including removing duplicates, OR conditions, and partial matches.

If you use Excel 365, learning how to design these solutions with dynamic array functions such as UNIQUE and FILTER can greatly reduce the need to rebuild your workbook structure later.

For an article covering only the basic concepts—helper columns, CHOOSE, and INDEX + MATCH—see How to Search with VLOOKUP Using Multiple Criteria (Basics) first.

Quick Fix — Three Advanced VLOOKUP Formulas for Multiple Criteria You Can Use Now

Pattern A: Use COUNTIFS + a Helper Column to VLOOKUP Only the First Match

Scenario

Column A: Order number
Column B: Customer code
Column C: Product code
Column D: Order date
Column E: Sales amount

Goal: Even when a customer code + product code combination appears multiple times, retrieve only the sales amount from the first order row.

Enter the combined key in E2:
```
=B2&"|"&C2
```

In F2, keep the key only for the first row:

=IF(COUNTIFS($B$2:B2,B2,$C$2:C2,C2)>1,"",E2)

Enter the customer code in H2 and the product code in I2, then enter the following in the result cell:
```
=VLOOKUP(H2&"|"&I2,$F$2:$G$1000,2,FALSE)
```

This pattern leaves the key only on the first row with COUNTIFS and clears the key for duplicate rows so VLOOKUP cannot see them.

Pattern B: Use CHOOSE for VLOOKUP with Multiple Criteria Without a Helper Column

When you cannot add a helper column, you can use CHOOSE to create a virtual table.

=VLOOKUP(
  H2&"|"&I2,
  CHOOSE({1,2}, $B$2:$B$1000&"|"&$C$2:$C$1000, $D$2:$D$1000),
  2,
  FALSE
)

There is no helper column on the actual worksheet, but CHOOSE creates a two-column virtual table containing the combined key and return value, so VLOOKUP works as usual.

Pattern C: Return Multiple-Criteria Results at Once with UNIQUE/FILTER (365/Dynamic Arrays)

If the same combination appears in multiple rows and you want to keep only one row for each combination:

=UNIQUE(B2:D1000)

If you want to filter all rows for a specific customer and product:

=FILTER(
  B2:E1000,
  (B2:B1000=H2) * (C2:C1000=I2),
  "Not found"
)

When you further process these filtered results with VLOOKUP, INDEX MATCH, or XLOOKUP, you can cleanly build the common pattern of “multiple criteria → full rows → extract only a specific column.”

Why Is Another Article Needed? — Defining the Scope of Advanced VLOOKUP for Multiple Criteria

The basics article covered multiple-criteria lookups using helper columns, CHOOSE, and INDEX MATCH. This article additionally covers the following:

Using COUNTIFS + VLOOKUP to retrieve only the first match
Handling multiple criteria with CHOOSE/INDEX MATCH without a helper column
How to divide tasks between UNIQUE and FILTER
Situations where you should not use VLOOKUP with multiple criteria for large datasets

Pattern 1 — Remove Duplicates and Retrieve Only the First Row with COUNTIFS + VLOOKUP

① Create the Sample Data Structure

Assume you have sales data like the following.

Row	A: Order Number	B: Customer Code	C: Product Code	D: Order Date	E: Sales Amount
2	O-1001	C001	P01	2025-01-01	50,000
3	O-1002	C001	P01	2025-01-05	55,000
4	O-1003	C001	P02	2025-01-03	70,000
5	O-1004	C002	P01	2025-01-02	60,000
6	O-1005	C002	P01	2025-01-10	65,000

② Create a Helper Column That Displays Only the First Row for Each Combination with COUNTIFS

Enter the combined key in F2:
```
=B2&"|"&C2
```

In G2, keep the key only for the first row:

=IF(COUNTIFS($B$2:B2,B2,$C$2:C2,C2)>1,"",F2)

Only the first row retains the key, as shown below.

Row	B: Customer	C: Product	F: Combined Key	G: First-Row Key Only
2	C001	P01	C001\|P01	C001\|P01
3	C001	P01	C001\|P01
4	C001	P02	C001\|P02	C001\|P02
5	C002	P01	C002\|P01	C002\|P01
6	C002	P01	C002\|P01

③ Formula to Search Only the First Row with VLOOKUP

Enter the customer code in I2 and the product code in J2, then enter the following formula in the result cell.

=VLOOKUP(I2&"|"&J2,$G$2:$H$1000,2,FALSE)

Place the “first-row key only” column in column G and the sales amount (or desired return column) in column H, and VLOOKUP will always find only the first order row.

④ Expanded Pattern for Three or More Criteria

The principle is the same even with three or more criteria, such as customer + product + color.

=IF(
  COUNTIFS($B$2:B2,B2,$C$2:C2,C2,$D$2:D2,D2)>1,
  "",
  B2&"|"&C2&"|"&D2
)

You can expand the lookup formula as follows.

=VLOOKUP(I2&"|"&J2&"|"&K2,$G$2:$H$1000,2,FALSE)

Pattern 2 — Multiple Criteria and Partial Matches with CHOOSE/INDEX MATCH Without a Helper Column

① Create a Virtual Table with CHOOSE

If you cannot add a helper column, you can create a virtual table with CHOOSE and use VLOOKUP.

=VLOOKUP(
  H2&"|"&I2&"|"&J2,
  CHOOSE(
    {1,2},
    $B$2:$B$1000&"|"&$C$2:$C$1000&"|"&$D$2:$D$1000,
    $E$2:$E$1000
  ),
  2,
  FALSE
)

② Handle Left-Lookups and Multiple Criteria with INDEX MATCH

Converting the same structure to INDEX MATCH makes it more flexible for looking up values to the left and inserting columns.

=INDEX(
  $E$2:$E$1000,
  MATCH(
    H2&"|"&I2&"|"&J2,
    $B$2:$B$1000&"|"&$C$2:$C$1000&"|"&$D$2:$D$1000,
    0
  )
)

More INDEX MATCH examples are covered in Master INDEX MATCH: Overcome the Limitations of VLOOKUP and XLOOKUP.

③ Notes When Mixing in Partial-Match (Wildcard) Criteria

It is safest to use a partial-match condition on only one main column.

=INDEX(
  $E$2:$E$1000,
  MATCH(
    "*"&H2&"*"&"|"&I2&"|"&J2,
    $B$2:$B$1000&"|"&$C$2:$C$1000&"|"&$D$2:$D$1000,
    0
  )
)

In this example, the formula finds a row where the product name contains the value in H2 and the color and size match exactly.

Pattern 3 — Dynamic Array Version: Divide Tasks Between UNIQUE and FILTER

① Keep Only One Row per Criteria Combination with UNIQUE

UNIQUE is convenient for quickly organizing data where the same combination appears more than once.

=UNIQUE(B2:D1000)

Alternatively, you can create a combined-key array and use UNIQUE to keep only one record for each customer + product combination.

② Return All Rows Matching Multiple Criteria with FILTER

A useful pattern is to narrow the results with FILTER first, then look up only the columns you need.

=FILTER(
  B2:E1000,
  (B2:B1000=H2) * (C2:C1000=I2),
  "Not found"
)

③ Finish with XLOOKUP/INDEX MATCH Instead of VLOOKUP

When working with dynamic arrays, XLOOKUP or INDEX MATCH can often be a more natural fit. For VLOOKUP with multiple criteria, see Complete Guide to XLOOKUP with Multiple Criteria; for a comparison with VLOOKUP, see Complete Guide to VLOOKUP vs. XLOOKUP.

When Should You Avoid VLOOKUP with Multiple Criteria? — Cases Better Suited to SUMPRODUCT or PivotTables

VLOOKUP is not always the right answer simply because multiple criteria are involved.

When you need to find a single value, such as a unit price → VLOOKUP/XLOOKUP/INDEX MATCH
When the goal is an aggregation such as a sum, count, or average → SUMIFS/SUMPRODUCT/PivotTable

If you are forcing complex aggregations into VLOOKUP with multiple criteria, consider redesigning your approach with the following articles.

Troubleshooting — Common Symptoms, Causes, and Fixes

Symptom	Cause	Fix
The COUNTIFS + VLOOKUP pattern always returns the second row	The COUNTIFS range is fixed to the full range rather than extending to the current row	Set the first reference in COUNTIFS as absolute and the second reference as relative so it counts only through the current row
All helper-column values appear the same, and duplicates are not removed	The combined key concatenates values without a separator, causing values to run together	Use a visible separator such as `"\|"` and standardize number/text formats
The CHOOSE pattern returns a #VALUE! error	The arrays passed to CHOOSE are different sizes	Check again that every range has the same number of rows
The INDEX MATCH multiple-criteria formula returns only #N/A	The MATCH match_type argument was omitted or set to 1/-1	Always use 0 (exact match) for multiple criteria
UNIQUE/FILTER returns a #NAME? error in older versions	The version does not support dynamic arrays or newer functions	Rebuild the same logic with classic functions such as PivotTables, VLOOKUP, or SUMIFS
VLOOKUP formulas with multiple criteria are slow and the workbook frequently freezes	You are referencing entire columns (such as B:B) or excessively large ranges	Specify only the actual data range, and use tables/structured references if needed
#N/A appears frequently, and the cause is hard to identify	Basic issues such as spaces, formatting (text vs. numbers), or sort status	See the Complete Guide to Fixing VLOOKUP #N/A Errors

Wrap-Up and Three Related Articles to Read Next

This article covered three patterns for combining COUNTIFS, CHOOSE, INDEX MATCH, and UNIQUE/FILTER from the perspective of advanced VLOOKUP for multiple criteria.

Bookmark ExcelJump to easily find future articles on advanced XLOOKUP applications and SUMPRODUCT with multiple criteria.

첫 번째 VLOOKUP 값만 불러오는 실무 수식 패턴