How to return the first item from a priority list that appears in another list
Formula (Excel and Google Sheets)
=XLOOKUP(TRUE,COUNTIF(C2:C5,A2:A6)>0,A2:A6,"None available")Check each priority item against the other list and return the first that is there: =XLOOKUP(TRUE,COUNTIF(C2:C5,A2:A6)>0,A2:A6,"None available").
- Result on the example
- Corvex in
E2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
COUNTIF(C2:C5,A2:A6) counts each preferred supplier in the in-stock list, one count per supplier in priority order. Acme and BrightCo count 0, Corvex counts 1. XLOOKUP returns the first supplier with a count above 0.
=XLOOKUP(TRUE,COUNTIF(C2:C5,A2:A6)>0,A2:A6,"None available")Result"Corvex"
C2:C54 rows × 1 col: "Delmar Office", "Corvex", "Evergreen Paper", "Foxhall Electric"A range: 4 cells in one column.
A2:A65 rows × 1 col: "Acme Supply", "BrightCo", "Corvex", "Delmar Office", …A range: 5 cells in one column.
COUNTIF(C2:C5,A2:A6){5 rows × 1 col} 0, 0, 1, 1, …COUNTIF: Counts cells that meet one condition.- range
C2:C54 rows × 1 col: "Delmar Office", "Corvex", "Evergreen Paper", "Foxhall Electric" - criteria
A2:A65 rows × 1 col: "Acme Supply", "BrightCo", "Corvex", "Delmar Office", …
- range
COUNTIF(C2:C5,A2:A6)>0{5 rows × 1 col} FALSE, FALSE, TRUE, TRUE, …The > operator tests greater than.
XLOOKUP(TRUE,COUNTIF(C2:C5,A2:A6)>0,A2:A6,"None available")"Corvex"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
TRUE - lookup_array
COUNTIF(C2:C5,A2:A6)>0{5 rows × 1 col} FALSE, FALSE, TRUE, TRUE, … - return_array
A2:A65 rows × 1 col: "Acme Supply", "BrightCo", "Corvex", "Delmar Office", … - if_not_found
"None available"
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(TRUE,COUNTIF(C2:C5,A2:A6)>0,A2:A6,"None available"). We ran it in Google Sheets mode on the same data and it returns Corvex.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(A2:A6,MATCH(TRUE,INDEX(COUNTIF(C2:C5,A2:A6)>0,0),0)) | Corvex | Every Excel version, without Ctrl+Shift+Enter. |
| =FILTER(A2:A6,COUNTIF(C2:C5,A2:A6)) | spills 3 × 1Different result | Lists every available supplier, still in priority order. Corvex, Delmar Office and Evergreen Paper. |
Common mistakes
- Search the priority list, not the in-stock list. Reversing them returns the first in-stock supplier in that list's order.
- Names must match exactly, apart from case.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Return the first item from a priority list that appears in another list
E2, return the most preferred supplier that appears in the in-stock list in C2:C5.Questions
How do I return the second choice too?
Take the first two from the FILTER: =TAKE(FILTER(A2:A6,COUNTIF(C2:C5,A2:A6)),2).
Can the condition be a stock quantity instead?
Yes: look up each supplier's stock and test it, for example XLOOKUP(A2:A6,C2:C5,D2:D5,0)>=G1.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.