Google Sheets Conditional Formatting With VLOOKUP Rules

Yes. In Google Sheets, use Format > Conditional formatting, choose Custom formula is, and enter a formula that uses VLOOKUP to return the condition you want. When the lookup table is on another sheet, wrap its range in INDIRECT; for example, you can highlight a row when its key has the status Active on a Lookup sheet.

What VLOOKUP adds to conditional formatting

Conditional formatting changes a cell’s appearance when a rule evaluates as true. VLOOKUP lets that rule check information stored in a table rather than hard-coding every key or status into the formatting rule. This is useful for highlighting orders by customer tier, inventory by category, or tasks by a status maintained in a separate reference list.

The two features have different jobs: VLOOKUP finds a value, while the conditional-formatting rule tests the result. For example, a lookup might retrieve the word Active. The rule then compares that result with Active and applies the selected fill or text style when they match. The formatting is visual; it does not change the underlying cell contents.

A common setup has a working sheet with identifiers in column A and details across columns A through D. A separate sheet named Lookup has identifiers in column A and statuses in column B. A custom formula can look up each working-sheet identifier and determine whether its status qualifies.

Prepare the lookup table

Before writing the rule, make sure the lookup table is laid out for VLOOKUP. The value being searched for must be in the leftmost column of the selected range. The value to return must be in a column to its right. In the example, Lookup!A:A contains the key and Lookup!B:B contains the status, so the two-column range is suitable.

Use one consistent identifier format in both places. Text such as 0017 and the number 17 are not necessarily equivalent for a lookup.
Remove accidental spaces and check for duplicate keys. VLOOKUP returns the first matching row when the lookup column contains duplicates.
Keep headers outside the lookup range when practical. A range beginning at row 2 avoids treating the header as a possible result.
Decide what should happen when a key is missing. A conditional-formatting rule should normally treat a missing lookup as false rather than display an error.

Exact matching is usually the right choice for identifiers and categories. In VLOOKUP, the final argument FALSE requests an exact match. TRUE, or omitting that argument, can use approximate matching and depends on the lookup column being sorted; that behavior is usually unsuitable for status labels or customer IDs.

Create the rule in Google Sheets

For a row-based rule, select the full range that should receive formatting, such as A2:D100. Then open Format > Conditional formatting. In the sidebar, confirm the range under Apply to range, open the Format cells if menu, and choose Custom formula is. Enter the formula, choose a formatting style, and select Done.

Select the rows or cells to format, starting at the same row used in the formula.
Open Format > Conditional formatting and choose Custom formula is.
Enter a formula that returns TRUE for a row that should be highlighted.
Choose a fill color, text style, or other available format, then select Done.

For this example, assume the working sheet has keys in A2:A100, the rows to format are A2:D100, and Lookup has keys in A2:A100 and statuses in B2:B100. The formula uses INDIRECT because the lookup table is on another sheet:

=AND($A2<>"",IFERROR(VLOOKUP($A2,INDIRECT("'Lookup'!$A$2:$B$100"),2,FALSE)="Active",FALSE))

The rule skips blank keys, searches for the key from column A in the Lookup table, and tests whether the returned second-column value is Active. IFERROR turns a missing key or lookup error into FALSE, so that row is not highlighted. AND combines this error handling with the nonblank-key check.

Why the dollar signs and row number matter

In a custom formula, Sheets evaluates the rule relative to the top-left cell of the selected range. With A2:D100 selected, the formula’s $A2 tells Sheets to check column A while allowing the row number to change. The dollar sign fixes the column; the missing dollar sign before 2 lets the reference become A3, A4, and so on as the rule evaluates other rows.

The lookup range is different: $A$2:$B$100 is fully fixed, so it does not move as the formatting rule is evaluated across the selected rows. If you select a range starting at row 3 but leave A2 in the formula, the rule may test the preceding row and appear inconsistent. Align the formula’s row with the first row of Apply to range.

If only the key cell should be formatted, apply the rule to A2:A100. If the entire row should be formatted, apply it to A2:D100 and keep the key reference anchored to column A. Selecting a range that includes headers can also lead to confusing results, so exclude the header row unless you specifically want it evaluated.

Use VLOOKUP when the table is on the same sheet

When the lookup table is on the same sheet, a direct range reference is enough; INDIRECT is not needed. Suppose the working rows are in A2:D100 and the lookup keys and statuses are in F2:G100. Apply the rule to A2:D100 and use this formula:

=AND($A2<>"",IFERROR(VLOOKUP($A2,$F$2:$G$100,2,FALSE)="Active",FALSE))

Here, the search starts in column F and returns a status from column G, the second column of the specified range. If the lookup table is instead arranged with the status to the left of the key, VLOOKUP cannot search left within that range. Rearrange the lookup table or choose a different lookup method.

For a single-cell condition, you can apply the rule just to the key column rather than a full row. The custom formula still needs to refer to the first cell in the selected range. Setting the Apply to range correctly is as important as selecting the right formula.

Common problems and how to fix them

The rule rejects a reference to another sheet: Conditional-formatting custom formulas cannot use an ordinary direct reference to a different sheet in the same way as a normal cell formula. Use INDIRECT with the other sheet’s range, as in the example, and check that the sheet name is spelled correctly.
Nothing is highlighted: Verify that the lookup value really matches, that the status text is exactly Active, and that the formula’s row number matches the top row of the applied range. Check that the rule is enabled and that the selected formatting style is visible.
Every row seems to use the same key: A reference such as $A$2 fixes both the column and row. For a row-by-row rule, use $A2 so the row can change.
Some rows show errors or fail unexpectedly: Confirm the key is in the first column of the lookup range and that the VLOOKUP column index is correct. In a two-column range, 2 returns the second column.
A value appears to match but does not: Check for leading or trailing spaces, inconsistent number-versus-text values, and duplicate lookup keys. Exact matching does not ignore all differences in the stored values.

For large sheets, avoid needlessly huge lookup ranges if the data is modest. A bounded range such as A2:B1000 is easier to inspect and can reduce unnecessary recalculation compared with referencing entire columns inside a rule. Expand the range when the lookup table grows, or choose a suitably generous limit and document it.

VLOOKUP, helper columns, and other approaches

A custom formula keeps the visual rule tied to the reference table, so a status change in that table can change the formatting without editing the rule. Its disadvantage is that the logic can be less obvious to someone reviewing the sheet, especially when INDIRECT and error handling are included.

A helper column is a useful alternative when the lookup result should also be visible or easy to audit. Put a normal VLOOKUP formula beside each working row, inspect the returned status, and create a simpler conditional-formatting rule based on that helper column. This adds a column but makes it clearer which value caused a row to be highlighted. Keep the helper formula’s row references aligned with the data and fill it down as needed.

If the goal is only to highlight keys that appear in a list, a membership check such as MATCH can express the task without retrieving a status field. VLOOKUP is preferable when the rule depends on a value returned from another column, such as Active or Pending. Whichever approach you use, test it on a few known matches, a known nonmatch, a blank key, and a missing key before relying on the formatting across a large range.

Frequently Asked Questions

Can Google Sheets conditional formatting use VLOOKUP?
Yes. Choose Custom formula is in the conditional-formatting sidebar and use a formula that evaluates to TRUE or FALSE. For a lookup on another sheet, use INDIRECT to supply the range.

Can I highlight an entire row based on a VLOOKUP result?
Yes. Set Apply to range to the full row area, such as A2:D100, and make the key reference lock its column but not its row, as in $A2. Start the formula at the first row in the selected range.

Why does a VLOOKUP conditional-formatting rule need INDIRECT?
Google Sheets restricts direct references to other sheets in conditional-formatting custom formulas. INDIRECT provides the other-sheet range as text that the formula can use. Check that the sheet name and range inside the text are accurate.

Does VLOOKUP need FALSE in this rule?
For IDs, names, and status tables, use FALSE to request an exact match. Approximate matching is intended for ordered lookup data and can return an unintended result if used with an unsorted list.

What happens when VLOOKUP finds no match?
Without error handling, the lookup can produce an error. Wrapping the test in IFERROR with FALSE as its fallback prevents a missing key from triggering the format or leaving an error in the rule.

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.