# How to make a lookup skip blank results in Excel

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.

**Excel:** `=XLOOKUP(1,(A2:A9=F1)*(C2:C9<>""),C2:C9,"No phone")` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Customer | Date | Phone |  | Customer | Litware |
| Contoso | 2026-02-03 | 555-0142 |  | Phone |  |
| Litware | 2026-02-05 |  |  |  |  |
| Fabrikam | 2026-02-09 | 555-0188 |  |  |  |
| Litware | 2026-02-12 |  |  |  |  |
| Contoso | 2026-02-16 |  |  |  |  |
| Litware | 2026-02-20 | 555-0173 |  |  |  |
| Northwind | 2026-02-23 | 555-0110 |  |  |  |
| Litware | 2026-02-27 | 555-0199 |  |  |  |

Formula in F2: `=XLOOKUP(1,(A2:A9=F1)*(C2:C9<>""),C2:C9,"No phone")`

Result: 555-0173

## 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.

## Other ways

- `=INDEX(C2:C9,MATCH(1,(A2:A9=F1)*(C2:C9<>""),0))`: For Excel 2019 and earlier (Ctrl+Shift+Enter in 2016 and older).
- `=XLOOKUP(F1,A2:A9,C2:C9)`: 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.

## 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<>""))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/lookup-skip-blank-results