Excel Practice
Lessons Lesson 43

XLOOKUP: Looking Left, Not Found and Next Larger

About this lesson

XLOOKUP takes its search column and return column separately. So it can return from the left. Its fourth argument is a not-found value. Its fifth is a match mode: -1 for next smaller, 1 for next larger.

By the end you can

  • return from a column to the left with XLOOKUP.
  • use the built-in not-found value.
  • round up to the next available size with match mode 1.

Practises XLOOKUP VLOOKUP INDEX MATCH

The idea

Three things VLOOKUP cannot do, each one argument in XLOOKUP. Returning from a column to the left of the search column: just name the columns. A not-found value: the fourth argument, no IFNA. Rounding up to the next size: match mode 1, the fifth argument. That is the smallest bundle, the next price break, the first slot after a time.

The mistake is the empty argument. To reach the fifth argument you skip the fourth. That is two commas in a row with nothing between them. Excel treats the gap as "not given", and it is correct. It just looks like a typo.

The mistake to watch for

Two commas in a row. To reach the match mode you skip the not-found argument. The empty argument looks like a typo, but it is correct. The real mistake is the sign. 1 rounds up to the next size, and -1 rounds down to the band. Both return a value that looks right.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

The IDs are in column B and the names in column A, to the left of them. In E2, find the name for the ID in D2 with XLOOKUP.

To begin, type it exactly:

=XLOOKUP(D2,B2:B5,A2:A5)

E2
Row A B C D E F G H
1 Name Staff ID Find ID Name Bundle size Order
2 Ada S104 S102 50 130
3 Ben S102 S999 100
4 Cai S110 S107 150 Bundle needed
5 Dee S107 250
6
7
8

Every step

  1. 1

    The IDs are in column B and the names in column A, to the left of them. In E2, find the name for the ID in D2 with XLOOKUP. To begin, type it exactly: =XLOOKUP(D2,B2:B5,A2:A5).

    Hint. Search B, return A.

  2. 2

    D3 holds an ID nobody has. In E3, look it up with XLOOKUP's fourth argument set to the text Unknown.

    Hint. Fourth argument, in quotation marks.

  3. 3

    Read =XLOOKUP(D4,B2:B5,A2:A5,"Unknown") and say what it returns.

    Hint. S107 is on the list.

  4. 4

    Stock comes in the bundle sizes in G2:G5, and an order is for 130 units. In H3, find the smallest bundle that covers the order. XLOOKUP with a match mode of 1, exact or next larger.

    Hint. Fifth argument 1.

  5. 5

    In H5, write a formula using XLOOKUP that gives the largest bundle size that does not exceed the order. That is the one that would be sent if the rest were topped up loose.

    Hint. Fifth argument -1.