Conditional Formatting Based on XLOOKUP Result – Excel

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Translated by

Dennis Madrid

Last updated on November 14, 2022

This tutorial will demonstrate how to apply Conditional Formatting based on XLOOKUP in Excel. If your version of Excel doesn’t support XLOOKUP or you’re using Google Sheets, read how to use the VLOOKUP instead.

conditional formatting based on xlookup

 

Let’s look at an example where we want to apply conditional formatting based on the result of a XLOOKUP Function.

conditional formatting based on xlookup ex2

 

Format based on XLOOKUP Comparison

First, we will apply conditional formatting to the table of Names and Student IDs (col E-H) by looking up each student’s grades (Col B-C) and apply RED cell Fill Color if their scores are below 72. The formula is shown here, but below we will walk through the steps.

=XLOOKUP($F3,$B$3:$B$7,$C$3:$C$7)<72

Edit formatting rule

 

Let’s look at how to apply the above formula in conditional formatting:

Apply Conditional Formatting

  1. Highlight the range where the conditional formatting will be applied.

Xlookup apply conditional formatting step1

  1. In the Ribbon, go to Home Tab > Styles Group > Conditional Formatting > New Rule.

Xlookup apply conditional formatting step2

 

  1. In the pop-up menu, select Rule Type: Use a formula to determine which cells to format.

Xlookup apply conditional formatting step3

 

  1. In the Formula Bar, input our formula.

Xlookup apply conditional formatting step4

 

  1. Click Format and define your formatting settings. In our case we will choose a RED Fill Color. Click OK.

Xlookup apply conditional formatting step5

  1. Now click OK and the Conditional Formatting Rule is applied.

Xlookup apply conditional formatting step6

 

Now that we know how to apply conditional formatting, let’s walk through the formula.

It can be helpful to start by entering your conditional formatting formula(s) into cells to test that they work as you’d expect. We will do so below.

 

XLOOKUP Function

First, we perform our XLOOKUP.

=XLOOKUP($F3,$B$3:$B$7,$C$3:$C$7)

Xlookup conditional formatting xlookup function

We use $s to lock cell references. This is essential to creating a formula that will work for all rows.

 

Logical Comparison

Next, the results are compared to the set criterion.

=H3<72

Xlookup conditional formatting logical comparison

 

Conditional Formatting will change the cell color for any rows with TRUE.

 

Combining all the formulas together yields our original XLOOKUP formula:

=XLOOKUP($F3,$B$3:$B$7,$C$3:$C$7)

 

Format If XLOOKUP is Blank

Notice in the previous example, one student did not have a score. We can detect and highlight these cells by adding the ISBLANK Function:

=ISBLANK(XLOOKUP($F3,$B$3:$B$7,$C$3:$C$7))

Format if xlookup is blank

 

This is how the formula will evaluate:

Isblank xlookup functions

 

Format If XLOOKUP is within Range of Values

Another common scenario with XLOOKUP is to check if the value is within a given range of values.

To do this, we can use the AND Function together with XLOOKUP:

=AND(XLOOKUP($F3,$B$3:$B$7,$C$3:$C$7)>=72,XLOOKUP($F3,$B$3:$B$7,$C$3:$C$7)<=74)

Format if xlookup is within range of values

 

Let’s walk through the formula:

 

Lower Boundary

First, we check if our XLOOKUP’s output is greater than or equal to a given lower boundary (e.g., 72).

=H3>=72

Xlookup lower boundary

Upper Boundary

Next, we check if that same value is less than or equal to the upper boundary (e.g., 74).

=H3<=74

Xlookup upper boundary

 

AND Function

Finally, we use the AND Function to check if both conditions are TRUE.

=AND(I3,J3)

Xlookup and function

 

Combining the above formulas results in our original formula:

=AND(XLOOKUP($F3,$B$3:$B$7,$C$3:$C$7)>=72,XLOOKUP($F3,$B$3:$B$7,$C$3:$C$7)<=74)

 

Conditional Formatting – Multiple XLOOKUP Conditions

You can easily add multiple conditional formatting rules:

conditional formatting based on xlookup menu

Conditional Formatting rules are applied in order (top to bottom). If Stop if True is checked, then if that condition is met no more formatting rules are tested or applied.

 

In our particular example, the blank XLOOKUP (e.g., ROW 6) satisfies two conditions, red (e.g., <72) and orange (e.g., ISBLANK). Because the ISBLANK rule is applied first and Stop if True is checked, ROW 6 is highlighted ORANGE because the ISBLANK condition is met and subsequent rules are not tested.

AI Formula Generator

Try for Free

See all How-To Articles