Conditional Logic & Validation
Conditional Formatting Based on XLOOKUP or VLOOKUP Result Across a Reference Table
Highlight order rows dynamically without adding clutter columns by writing an XLOOKUP (or IFNA + VLOOKUP) conditional-formatting rule that checks credit-hold or recall status in a master vendor/SKU table.
Cross-table highlighting without helper columns: always lock the lookup table and trap #N/A!
Suppose your Order Entry sheet lists SKU_Code in column A, while a separate Master_Catalog block $D$2:$E$5 tracks Compliance_Status ("Active", "Credit_Hold", "Recalled")! You can color entire order rows red whenever their SKU is Recalled or on Hold WITHOUT adding a visible status column to the order table—by putting XLOOKUP directly inside a custom Conditional Formatting formula!
Two rules prevent broken cross-table formatting: (1) Lock the lookup key’s column =$A2 so all columns A:B in row 2 look up the same SKU, while locking BOTH columns and rows on the lookup array $D$2:$D$5 and return array $E$2:$E$5! (2) Supply XLOOKUP’s 4th if_not_found argument (e.g., "Unlisted")—or wrap VLOOKUP($A2, $D$2:$E$5, 2, FALSE) in IFNA(..., "Unlisted")—so unmatched SKUs return a clean string instead of #N/A!
| Cross-Table Formatting Goal on Selection A2:B20 | Custom Rule Formula | Unmatched Key Behavior |
|---|---|---|
| Highlight Row if Master Status = "Recalled" (XLOOKUP) | =XLOOKUP($A2, $D$2:$D$5, $E$2:$E$5, "Unlisted") = "Recalled" | Clean FALSE (No #N/A!) |
| Highlight Row if Unlisted / Missing from Master Catalog | =COUNTIF($D$2:$D$5, $A2) = 0 | TRUE for Rogue / Unregistered SKUs |
| Legacy VLOOKUP Equivalent | =IFNA(VLOOKUP($A2, $D$2:$E$5, 2, FALSE), "Unlisted") = "Recalled" | Identical Cross-Version Result |
Row-Highlight Rule Based on XLOOKUP Master Status = "Recalled"
Returns TRUE across every column of row 2 when SKU $A2 maps to "Recalled" in master table $D$2:$E$5.
=XLOOKUP($A2, $D$2:$D$5, $E$2:$E$5, "Unlisted") = "Recalled"Combined Order Shipment Gate (Flag Recalled OR Unregistered Master SKUs)
Classifies each order line against the master status table as Cleared, Blocked (Recalled/Hold), or Unregistered SKU.
=IF(XLOOKUP($A2, $D$2:$D$5, $E$2:$E$5, "Unlisted") = "Active", "Cleared to Ship", "Flagged: " & XLOOKUP($A2, $D$2:$D$5, $E$2:$E$5, "Unregistered_SKU"))Try the example
Use the original values or change them to test your understanding.