VLOOKUP with Two Lookup Values in Google Sheets Explained

Yes. To use VLOOKUP with two lookup values in Google Sheets, combine the values into one key on both sides of the lookup. If your criteria are in A2 and B2, your source criteria are in D and E, and the answer is in F, this formula returns the row where both criteria match: =VLOOKUP(A2&"|"&B2,{$D$2:$D$100&"|"&$E$2:$E$100,$F$2:$F$100},2,FALSE).

How VLOOKUP handles two criteria

VLOOKUP accepts one lookup value. It searches for that value in the first column of a range, then returns a value from a column to its right. It does not have separate arguments for two conditions, such as product and color, or surname and department.

Combining the criteria solves this limitation by making a single key. For example, if a row contains the product Mug and the color Blue, joining those values with a separator creates the key Mug|Blue. The formula builds that key from A2 and B2, creates matching keys from columns D and E, then returns the corresponding value from F. With Mug|Blue on the matching source row and 18 in F, the result is 18.

The final argument, FALSE, asks for an exact match. That matters for criteria such as names, categories, and codes: an approximate match can return an unintended row. The lookup range also needs to start with the combined key and include the result column to its right.

Use a combined key directly in the formula

The opening formula constructs a two-column virtual lookup table: the first column is made by joining D and E, and the second is F. That lets you avoid adding a permanent helper column. It is convenient for a one-off lookup or a compact sheet, and it works even when the two criteria columns are separated from the return column.

In the example, A2 contains Mug, B2 contains Blue, D2:D100 contains product names, E2:E100 contains colors, and F2:F100 contains prices. The formula finds the combined key Mug|Blue and returns the price in the same source row. If that row’s price is 18, the result is 18.

Use a separator between values. Joining them without one can create ambiguous keys: AB plus C and A plus BC both become ABC. A pipe character is often a readable choice, but it is not magic; if a criterion itself may contain a pipe, two different pairs could still produce the same combined text. For ordinary labels, a separator is usually sufficient. For critical data with unrestricted text, use a multi-condition method such as INDEX and MATCH instead.

In Google Sheets, commas separate function arguments in the examples here. Array-literal separators can vary with spreadsheet locale, so if the inline formula gives a parse error, check the locale and its formula conventions. A helper column avoids the more complex array literal and is often easier to troubleshoot.

Build a helper column for a reusable lookup

A helper column makes the key visible and easier to inspect. Suppose the source criteria are in D and E, the return values are in F, and you put the combined key in G. In G2, with the first source record in row 2 and the criteria in D2 and E2, enter:

=D2&"|"&E2

Fill that formula down through the source records. Put the values you want to return in a column to the right of G, such as H. H can contain the same values as F, either by copying them or linking each row to its F value. Then, with A2 and B2 holding the lookup criteria and G2:H100 holding the combined keys and return values, use:

=VLOOKUP(A2&"|"&B2,$G$2:$H$100,2,FALSE)

If the matching source row has Mug in D, Blue in E, and 18 in F, with the corresponding answer in H, this returns 18. The number 2 tells VLOOKUP to return the second column of G:H. Keep the dollar signs on the lookup range if you plan to copy the formula down, so the source range does not shift.

A helper column is especially useful when many formulas need the same pair of criteria. You can see whether the generated key is correct, sort or filter the key column, and reuse it in other formulas. Its trade-off is that the sheet needs an additional column and that column must stay populated when new source rows are added.

Make sure the match is exact

For two-condition lookups, use FALSE as the last VLOOKUP argument. This tells Sheets to look for an exact combined key. Leaving that argument out can invoke approximate-match behavior, which is intended for ordered ranges and can produce incorrect or confusing results for ordinary text lookups.

Exact matching does not mean that Sheets will repair inconsistent data. Extra spaces, different punctuation, or different wording can prevent a match. For instance, Blue and Blue followed by a space are not the same text. Similarly, the number 12 and the text value “12” may behave differently in some comparisons. Make sure the lookup inputs and source columns use consistent formats and spelling.

When users type criteria manually, consider using dropdowns to reduce spelling variation. Google Sheets offers dropdowns through Insert > Dropdown, and data validation settings can also be accessed from Data > Data validation. These are optional data-entry controls, not a requirement for VLOOKUP.

What happens when there are duplicate pairs?

VLOOKUP returns the first matching row it encounters, reading down the first column of its lookup range. If Mug|Blue appears more than once, the formula does not combine the results or tell you that the key is duplicated; it returns the value associated with the first occurrence. The order of the source rows can therefore affect the result.

Decide what duplicates mean before relying on the answer. If each pair should identify exactly one record, check for duplicates and correct the underlying data. If multiple records are expected, VLOOKUP may not be the right tool because it returns only one result. If you want every matching row, use FILTER instead.

With criteria in A2 and B2, source criteria in D2:D100 and E2:E100, and results in F2:F100, this FILTER formula returns all matching values:

=FILTER($F$2:$F$100,$D$2:$D$100=A2,$E$2:$E$100=B2)

If two rows match, the formula spills both values into the cells below or beside the formula, provided those cells are clear. If no rows match, FILTER returns an error; you can handle that separately if a blank or message is preferable. Use VLOOKUP when a single first match is the intended outcome, and FILTER when the full set of matches is useful.

Troubleshoot no-match and wrong-result problems

A #N/A result usually means the combined lookup key was not found. Compare the two input values with the source row, and inspect the helper key if you have one. Check for trailing spaces, inconsistent capitalization if your data has been transformed, mismatched number-versus-text formats, and a source range that ends before the relevant row.

Check the key construction: Use the same separator and the same order of criteria in the lookup formula and helper column. Joining product then color is different from joining color then product.
Check the range: The leftmost column of the VLOOKUP range must contain the combined key, and the return column must be included to its right.
Check the column index: The index counts columns inside the selected range, not worksheet column letters. In G:H, H is column 2 of the range.
Check for collisions: If values can contain the separator, different pairs can create the same combined text. Choose another method or normalize the data.

To show a friendlier message for a missing key, with the same A2:B2 criteria and D:E:F source layout as the opening example, wrap the lookup in IFNA:

=IFNA(VLOOKUP(A2&"|"&B2,{$D$2:$D$100&"|"&$E$2:$E$100,$F$2:$F$100},2,FALSE),"No match")

This displays No match when VLOOKUP returns its not-found error. It does not correct a wrong key or hide every possible formula problem, so use it after checking the source data rather than as a substitute for troubleshooting.

Alternatives when a combined key is not ideal

INDEX and MATCH can test two criteria separately, avoiding a text key and its separator. With lookup criteria in A2 and B2, source criteria in D2:D100 and E2:E100, and return values in F2:F100, use:

=INDEX($F$2:$F$100,MATCH(1,($D$2:$D$100=A2)*($E$2:$E$100=B2),0))

The comparisons produce arrays of TRUE and FALSE values; multiplying them makes a 1 only where both conditions are true. MATCH finds the first 1, and INDEX returns the value from the corresponding position in F. If the matching row contains 18, this returns 18. Like VLOOKUP, it returns the first match if duplicates exist. In Google Sheets, this array-style formula can be entered normally; it does not require the legacy Ctrl+Shift+Enter step used by some older spreadsheet workflows.

Another option is FILTER, described above, when you want multiple results rather than the first. VLOOKUP remains a reasonable choice when a single result is expected and a combined key is clear. The best method depends on whether you need one match or all matches, whether the key is safe to combine, and whether a visible helper column would help maintain the sheet.

Frequently Asked Questions

Can VLOOKUP search two columns separately in Google Sheets?

Not as two independent criteria in its standard lookup argument. Combine both values into one key, use a helper column, or use a multi-condition formula such as INDEX and MATCH. Each method can match a row only when both conditions are satisfied.

Does VLOOKUP return the first match or every match?

It returns the first matching row in the lookup range. If you need all rows that meet both conditions, use FILTER with one condition for each source column instead.

Can the two lookup columns be far apart?

Yes. A combined-key formula can join criteria from separate columns, even when the return column is elsewhere. With a helper-column approach, place the key at the start of the lookup range and the value to return in a column to its right.

Why does my two-value lookup return #N/A?

The combined key may differ because of spaces, a different separator, swapped criteria, inconsistent data types, or an incomplete lookup range. Compare the constructed key with the source row and confirm that the formula uses an exact match.

Will the same formula work in Excel?

The general idea of joining two criteria into one key works in Excel too, but array-literal syntax and some formula details can differ between Excel and Google Sheets. The formulas here are written for Google Sheets in a web browser; if you use Excel, use its syntax and test the formula in that application.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.