Lookup & Reference
Find First Match Between Two Ranges (Priority-Ordered List Intersection)
Return the highest-priority item from List A that also appears anywhere in List B using a COUNTIF membership flag with INDEX and FILTER, with a clean fallback when the two lists are disjoint.
Pick the top-ranked candidate, warehouse, or backup vendor available right now!
Suppose Column A lists your preferred fulfillment warehouses in strict routing priority order (WH-Seattle, WH-Denver, WH-Dallas, WH-Atlanta), while Column D lists the warehouses that currently have SKU-900 in stock (WH-Miami, WH-Dallas, WH-Atlanta). Which warehouse wins the order? You want the FIRST entry in Priority List A that also exists anywhere in Available List D!
Evaluating a clean per-row membership flag B2 = COUNTIF($D$2:$D$4, A2) marks each priority option with 1 (in stock) or 0 (out of stock). Wrapping INDEX(FILTER(A2:A5, B2:B5 > 0), 1) inside an IF(SUM(B2:B5) = 0, "No Common Match", ...) guard immediately extracts the highest-priority overlapping item ("WH-Dallas")—and safely reports "No Common Match" whenever the two lists have zero overlap!
| Priority Rank | Preferred Warehouse (Col A) | Available In-Stock List (Col D) | Membership Flag B & Winner |
|---|---|---|---|
| Priority #1 | WH-Seattle | WH-Miami | B2 = 0 (Not in stock) |
| Priority #2 | WH-Denver | WH-Dallas | B3 = 0 (Not in stock) |
| Priority #3 | WH-Dallas | WH-Atlanta | B4 = 1 -> FIRST MATCH WINNER! |
| Priority #4 | WH-Atlanta | — | B5 = 1 (Backup match #2) |
Return First Overlapping Item in List-A Priority Order
Checks SUM(B2:B5) > 0 for at least one shared item, then takes element 1 of FILTER(A2:A5, B2:B5 > 0).
=IF(SUM(B2:B5) = 0, "No Common Match", INDEX(FILTER(A2:A5, B2:B5 > 0), 1))Count Total Overlapping Items Between Both Ranges
Sums the row-level membership flags (assuming distinct priority entries in A2:A5).
=SUM(B2:B5)Try the example
Use the original values or change them to test your understanding.