Function Problems

How to Use VLOOKUP and Conditional Formatting to Highlight Statuses in Excel

Elise Williams Oct 20, 2026 868 views

Question details

The user wants to retrieve data from multiple worksheets using VLOOKUP and automatically color-code the cells based on specific text statuses like "without contract" or "completed".

How to Use VLOOKUP and Conditional Formatting to Highlight Statuses in Excel

Product: Excel
Device & OS: not provided
Scenario: Managing and tracking project or item statuses across different sheets while visually distinguishing specific outcomes using dynamic fill colors.
Observed behavior: Cells successfully pull text values from other sheets and dynamically change their background color based on the retrieved status phrasing.

Before you start

Ensure your lookup values (such as IDs or names) exactly match across all your worksheets so VLOOKUP can successfully retrieve the data without returning #N/A errors.

Solution 1Recommended

Combine VLOOKUP with Custom Conditional Formatting Rules

Retrieve matching values using the VLOOKUP formula, then apply formula-based conditional formatting to colorize specific status words dynamically.

By nesting SEARCH inside ISNUMBER within your conditional formatting rules, Excel can identify specific text strings even if they are part of a longer sentence. This makes the formatting highly resilient and automatic as VLOOKUP updates the cell values.

Insert the VLOOKUP formulas

On Sheet 1, click cell B2 and enter `=VLOOKUP(A2,Sheet2!A:B,2,FALSE)`. Drag the fill handle down to copy the formula for the entire column. To retrieve data from the third sheet, enter `=VLOOKUP(A2,Sheet3!A:B,2,FALSE)` in cell C2 and copy it down.

Access the Conditional Formatting menu

Select the range containing your formula results, such as B2:C5. Navigate to the Home tab on the ribbon, click Conditional Formatting, and select New Rule from the dropdown menu.

Apply a rule for the red fill

Choose 'Use a formula to determine which cells to format'. In the formula box, enter `=ISNUMBER(SEARCH("without contract",B2))`. Click Format, go to the Fill tab, select a red color, and click OK.

Add additional rules for yellow and green fills

Repeat the New Rule process for your other statuses. Enter `=ISNUMBER(SEARCH("missing information",B2))` and apply a yellow fill. Finally, create a third rule using `=ISNUMBER(SEARCH("completed",B2))` and apply a green fill.

Combine VLOOKUP with Custom Conditional Formatting Rules

Relative References: Ensure that the cell reference in your ISNUMBER formula (e.g., B2) does not contain absolute reference dollar signs (like $B$2) so the rule can correctly apply to every cell in your selected range.

Seamless Data Management

Easily Apply VLOOKUP and Conditional Formatting in WPS Spreadsheet

WPS Spreadsheet fully supports advanced data lookup functions and formula-driven conditional formatting rules. It provides a familiar interface to efficiently track, analyze, and highlight your data across multiple sheets.

1. Open your workbook: Launch WPS Office and open your spreadsheet file containing the multiple worksheets.
2. Retrieve data with VLOOKUP: Select your target cell, go to the Formulas tab, and insert your VLOOKUP formula to pull data from adjacent sheets.
3. Set up Conditional Formatting: Highlight your data range, navigate to the Home tab, and select Conditional Formatting > New Rule to apply your ISNUMBER status color formulas.

Free and lightweight spreadsheet toolFully compatible with Microsoft Excel (.xlsx) formatsIntuitive Conditional Formatting rule managerAdvanced formula support including VLOOKUP, HLOOKUP, and XLOOKUP

microsoft office alternative - wps office

Frequently Asked Questions

Is the SEARCH function in my conditional formatting rule case-sensitive?

No, the SEARCH function ignores uppercase and lowercase letters. If you need your status highlighting to be strictly case-sensitive, replace SEARCH with FIND in your rule, for example: =ISNUMBER(FIND("Completed",B2)).

Why aren't my conditional formatting colors updating when the VLOOKUP result changes?

Your workbook's calculation options might be set to Manual. Go to the Formulas tab on the ribbon, click Calculation Options, and ensure Automatic is selected so the sheet refreshes formatting immediately when data changes.

Can I highlight the entire row based on the VLOOKUP status instead of just the single cell?

Yes. To highlight the entire row, lock the column reference in your conditional formatting formula. For example, use =$B2 instead of B2 in the ISNUMBER formula, and ensure the rule's 'Applies to' range spans across all the columns in your data table.

What if VLOOKUP returns an #N/A error, how do I prevent the cell from turning a weird color?

You can wrap your VLOOKUP formula in an IFERROR function to handle missing data cleanly. For example, =IFERROR(VLOOKUP(A2,Sheet2!A:B,2,FALSE), "Not Found"). Then you can optionally create a separate conditional formatting rule for the text "Not Found".

Excel & Data Problems