Function Problems

How to Apply Conditional Formatting for IFERROR and VLOOKUP Results in Excel

Jason Miller Oct 19, 2026 868 views

Question details

The user needs to highlight cells where a primary VLOOKUP fails and a secondary VLOOKUP succeeds, effectively distinguishing fallback data generated by an IFERROR or nested IF setup.

How to Apply Conditional Formatting for IFERROR and VLOOKUP Results in Excel

Product: Excel
Device & OS: not provided
Scenario: Applying advanced conditional formatting rules to visually distinguish fallback lookup results from primary lookup results across a dataset.
Observed behavior: Conditional formatting rules require a TRUE or FALSE outcome. Because a standard VLOOKUP returns data instead of a boolean value, it cannot directly trigger a conditional format without logical wrappers like ISNA or IF.

Before you start

Ensure your primary and secondary lookup tables are properly structured, and decide whether you want to highlight cells only when the secondary VLOOKUP succeeds, or simply whenever the primary VLOOKUP fails.

Solution 1Recommended

Use Nested IF and ISNA Functions for Conditional Formatting

Create a conditional formatting rule using a custom formula that evaluates the success of nested VLOOKUPs using ISNA.

Because conditional formatting requires a TRUE or FALSE outcome, a direct VLOOKUP cannot trigger the rule. By combining IF and ISNA functions, you can test if the first lookup returns an #N/A error. If it does, the formula checks the second lookup and returns TRUE to trigger the formatting.

Select the Target Range

Select the range of cells you want to format (for example, starting from row 3).

Open Conditional Formatting

Navigate to the Home tab on the Excel ribbon, click on Conditional Formatting, and select New Rule.

Choose Formula Option

Select 'Use a formula to determine which cells to format' from the list of rule types.

Enter the Logical Formula

Enter the formula testing the condition. For example: =IF(ISNA(VLOOKUP($B3,'Primary Table'!$B:$G,6,FALSE))=FALSE,FALSE,IF(ISNA(VLOOKUP($B3,'Fallback Table'!$A:$D,4,FALSE))=FALSE,TRUE,FALSE))

Apply the Format

Click the Format button, choose your desired highlight color or font style in the Format Cells dialog box, and click OK twice to apply the rule.

Use Nested IF and ISNA Functions for Conditional Formatting

Use Mixed Absolute References: Ensure you use a mixed reference like $B3 for the lookup value. This locks the lookup column so the formatting correctly applies across multiple columns while adjusting correctly row by row.

Solution 2

Highlight Any Primary Lookup Failure

Use a simpler formula to highlight a cell anytime the primary VLOOKUP does not find a match, regardless of whether the secondary fallback lookup succeeds.

Easily Manage Advanced Formulas and Formatting in WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical formulas like VLOOKUP, ISNA, and IFERROR, making it simple to construct complex conditional formatting rules across large datasets.

1. Open WPS Spreadsheet: Launch WPS Office and open your spreadsheet containing the lookup tables.
2. Access Conditional Formatting: Go to the Home tab and click on Conditional Formatting to create a new custom rule.
3. Apply Lookup Logic: Enter your combined VLOOKUP and ISNA formula to automatically highlight fallback data cells.

100% compatible with Microsoft Excel conditional formatting and complex logical formulas.User-friendly interface for managing multiple conditional formatting rules effortlessly.High calculation performance even when processing massive VLOOKUP tables.

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't my IFERROR function work directly in the conditional formatting rule?

Conditional formatting requires rules to evaluate to either TRUE or FALSE. An IFERROR function generally outputs a specific text value or alternative lookup result rather than a boolean. You must wrap your lookups in logical tests, such as IF and ISNA, to output TRUE or FALSE.

Can I use structured table references in conditional formatting formulas?

Conditional formatting rules generally do not accept structured table references (like Table1[Column1]). You must use standard cell ranges (like $A$2:$B$10) or full column references (like $A:$B), even if your data is formatted as an official table.

How do I ensure the conditional formatting applies to the whole row instead of just one cell?

To format an entire row, highlight the full row range before creating the rule. Additionally, make sure to lock the column of your lookup value by adding a dollar sign (e.g., use $B3 instead of B3) so the logical test always references the correct lookup column as it evaluates across the row.

Excel & Data Problems