How to VLOOKUP with Multiple Criteria in Google Sheets (Top 3 Methods)

The standard VLOOKUP function in Google Sheets is built to search for a single key inside a single column. However, real-world reporting frequently demands matching records against two or more conditions simultaneously—such as looking up an employee's bonus based on both their Department and Job Level, or retrieving product prices based on Item Name and Size.

Because traditional VLOOKUP cannot evaluate multiple columns natively, you need specific formulas to bypass this limitation. In this tutorial, you will learn the top three methods to look up data with multiple criteria: the reliable Helper Column method, the dynamic Array Formula approach, and the modern XLOOKUP formula.

Method 1: The Helper Column (Simple & Reliable)

The easiest and fastest method—especially for large spreadsheets—is creating a "helper column" that merges your lookup criteria into a single combined key.

Step 1: Create the Combined Key

Insert a blank column to the far left of your dataset (Column A). Combine your two criteria columns using an ampersand (&):

=B2 & "-" & C2

If Column B is "Shoes" and Column C is "Size 10", cell A2 will output Shoes-Size 10.

Step 2: Run a Standard VLOOKUP

When searching for the price, merge your search targets in the same order:

=VLOOKUP(F2 & "-" & G2, A2:D100, 4, FALSE)

Adding a hyphen ("-") between the merged values prevents accidental false matches (e.g., preventing "A1" + "23" from matching "A12" + "3").

Method 2: Multi-Criteria VLOOKUP Without Helper Columns (Array Trick)

If your spreadsheet permissions do not allow adding new columns, you can simulate a helper column inside Google Sheets memory using curly brackets {}:

=ARRAYFORMULA(VLOOKUP(F2 & G2, {A2:A100 & B2:B100, C2:C100}, 2, FALSE))

How This Formula Works:

• {A2:A100 & B2:B100, C2:C100} builds a virtual two-column table in memory.
• Column 1 of the virtual table contains the merged values of A and B.
• Column 2 contains the return values from Column C.
• The VLOOKUP returns the value from virtual column 2 where both conditions match.

Method 3: The Modern Solution (Using XLOOKUP)

If you prefer a cleaner formula without memory hacks or helper columns, use XLOOKUP. Unlike VLOOKUP, XLOOKUP natively handles multi-condition boolean logic using simple multiplication (*):

=XLOOKUP(1, (A2:A100=F2) * (B2:B100=G2), C2:C100)

Why This Is the Recommended Method:

• No Helper Columns: Keeps your raw data layout completely untouched.
• Logical Boolean Filtering: (A2:A100=F2) * (B2:B100=G2) creates an array of 1s (TRUE) and 0s (FALSE). XLOOKUP simply finds the row with the value 1.
• Leftward Lookups: Works regardless of whether your return column sits to the left or right of the lookup columns.

Quick Method Comparison

Frequently Asked Questions

What happens if multiple rows meet all my criteria?

Both VLOOKUP and XLOOKUP return the first matching record from top to bottom. If you need to return all matching rows, switch to the FILTER function instead: =FILTER(C2:C100, A2:A100=F2, B2:B100=G2).

Why am I getting a #N/A error?

Ensure there are no accidental trailing spaces in either your search keys or the raw table cells. You can wrap ranges in TRIM() or run a cleanup routine to strip hidden spaces.

Recommended Next Steps

Connecting multi-condition lookups allows you to build reliable, error-free databases. Explore these related spreadsheet tutorials:

• Core Lookup Fundamentals: Master the key differences and performance benchmarks between standard functions in our guide on XLOOKUP vs VLOOKUP in Google Sheets.
• Summing with Multiple Conditions: Need to add dollar amounts rather than pulling individual text records? Check out our tutorial on how to use SUMIF and SUMIFS in Google Sheets.
• Count Distinct Matches: Learn how to count unique items meeting specific criteria with our guide on how to count unique values in Google Sheets.
• Automate Billing Systems: Pull inventory items into customer receipts automatically using our tutorial on building an automated invoice generator in Google Sheets.