How to make a lookup skip blank results in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(1,(A2:A9=F1)*(C2:C9<>""),C2:C9,"No phone")Add a "not blank" test to the lookup: =XLOOKUP(1,(A2:A9=F1)*(C2:C9<>""),C2:C9,"No phone") returns the first match that has a value.
- Result on the example
- 555-0173 in
F2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
How it works
A plain lookup stops at the first Litware row, which has no phone, and shows 0. Multiplying (A2:A9=F1) by (C2:C9<>"") gives 1 only on Litware rows that have a number, so XLOOKUP skips the empty ones and returns 555-0173.
=XLOOKUP(1,(A2:A9=F1)*(C2:C9<>""),C2:C9,"No phone")Result"555-0173"
A2:A98 rows × 1 col: "Contoso", "Litware", "Fabrikam", "Litware", …A range: 8 cells in one column.
F1"Litware"The value in F1.
A2:A9=F1{8 rows × 1 col} FALSE, TRUE, FALSE, TRUE, …The = operator tests whether the two sides are equal.
C2:C98 rows × 1 col: "555-0142", (empty), "555-0188", (empty), …A range: 8 cells in one column.
C2:C9<>""{8 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …The <> operator tests whether they differ.
A2:A9=F1)*(C2:C9<>""{8 rows × 1 col} 0, 0, 0, 0, …The * operator multiplies.
XLOOKUP(1,(A2:A9=F1)*(C2:C9<>""),C2:C9,"No phone")"555-0173"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
1 - lookup_array
A2:A9=F1)*(C2:C9<>""{8 rows × 1 col} 0, 0, 0, 0, … - return_array
C2:C98 rows × 1 col: "555-0142", (empty), "555-0188", (empty), … - if_not_found
"No phone"
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(1,(A2:A9=F1)*(C2:C9<>""),C2:C9,"No phone"). We ran it in Google Sheets mode on the same data and it returns 555-0173.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(C2:C9,MATCH(1,(A2:A9=F1)*(C2:C9<>""),0)) | 555-0173 | For Excel 2019 and earlier (Ctrl+Shift+Enter in 2016 and older). |
| =XLOOKUP(F1,A2:A9,C2:C9) | 0Different result | Shown for contrast: the first Litware row is blank, and Excel shows a blank result as 0. Returns 0 from the first, empty Litware row. |
Common mistakes
- A cell with a space isn't blank, so it would be returned. Use
TRIM(C2:C9)<>""if that's possible. - If none of the customer's rows has a phone, the 4th argument, "No phone", is shown.
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.
Make a lookup skip blank results
F2, return the first phone number on file for the customer in F1, skipping rows where it is blank.Questions
How do I get the most recent non-blank value instead?
Search from the bottom: =XLOOKUP(1,(A2:A9=F1)*(C2:C9<>""),C2:C9,"No phone",0,-1).
How do I list all the non-blank values for the customer?
Use FILTER with both tests: =FILTER(C2:C9,(A2:A9=F1)*(C2:C9<>"")).
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.