XLOOKUP: handling missing values

1 / 5

Each price change is added at the bottom, so most products appear more than once. Headset is in rows 8 and 12, for example.

keyboard Typing formulas is easiest on a computer with a keyboard.

A1
ABCDEFG
1WeekProductPriceProductLatest price
2Week 1Monitor Stand33.99Notebook A5
3Week 2Office Chair188.5Headset
4Week 3Bookshelf151.49Office Chair
5Week 4Bookshelf156.49Bookshelf
6Week 5USB-C Hub35.25Monitor Stand
7Week 6USB-C Hub41.25
8Week 7Headset54.25
9Week 8Headset55.25
10Week 9Bookshelf158.49
11Week 10Office Chair191.5
12Week 11Headset57.25
13Week 12Office Chair192.5
14
15
16
17
old price
latest
Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.
Study notes

How XLOOKUP works

  • What are XLOOKUP's extra parts?

    After the three you know, XLOOKUP has three optional ones:

    =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

  • What does if_not_found do?

    It is what to show when the value isn't there, instead of #N/A: "Not found", a blank "", or 0. It does the job of IFNA without a second function.

  • What does match_mode do?

    • 0: exact match (the default).
    • -1: exact match, or the next smaller value (bands, like VLOOKUP with TRUE).
    • 1: exact match, or the next larger value.
    • 2: wildcards, so "Acme*" matches anything starting with Acme.

  • What does search_mode do?

    • 1: search from the top (the default). XLOOKUP stops at the first match.
    • -1: search from the bottom up, so it finds the last match.

    In a log where new rows are added at the bottom, such as price changes or stock counts, the last match is the latest one. That's something VLOOKUP can't do at all.

  • Can I skip parts in the middle?

    Yes, leave them empty between commas: =XLOOKUP(E2,B2:B13,C2:C13,,,-1). Writing 0 for match_mode makes it easier to read.

Know someone fighting with a spreadsheet? Send them this page.