Spreadsheet Practice

Lesson 17 of 45, Lookups

Last match, nearest value & wildcards

Quick answer

XLOOKUP can search from the bottom, round up to the next size, and match part of a name.

Key formula
=XLOOKUP(G2,B2:B9,C2:C9,"none",0,-1) (Latest match: search from the bottom)
Pro10 minutes, 3 exercisesXLOOKUPmatch_modesearch_mode

Watch it done

3 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

XLOOKUP takes two optional settings at the end: match_mode and search_mode. They turn it from "find this exact value" into three more useful lookups.

Latest entry. A lookup returns the first match from the top. Set search_mode to -1 and it starts at the bottom, so a purchase log gives you the most recent cost for an item.

Nearest value. match_mode 1 returns the exact match or the next larger value; -1 the next smaller. That is how you pick the smallest box that fits, or the tax band an income falls into, without sorting the table first.

Wildcards. match_mode 2 treats * as "any text" and ? as "one character", so "*"&G2&"*" finds a row containing what someone typed.

Syntax

=XLOOKUP(G2,B2:B9,C2:C9,"none",0,-1)Latest match: search from the bottom
=XLOOKUP(G2,A2:A6,B2:B6,"Too big",1)Exact or next larger
=XLOOKUP(G2,A2:A6,B2:B6,"Too small",-1)Exact or next smaller
=XLOOKUP("*"&G2&"*",A2:A6,B2:B6,"No match",2)Contains the typed text

Watch out for

  • The arguments are in a fixed order: if_not_found, then match_mode, then search_mode. To set match_mode, give if_not_found a value (or leave it as an empty argument).
  • Wildcard matching only works with match_mode 2. In the default mode, * is just a star.
  • "Exact or next larger" can return something far from your value. Check the table covers the range you expect.

The exercises

  1. Latest cost. In G3, return the most recent unit cost for the item in G2. The log is in date order, newest at the bottom.
  2. Smallest box that fits. In G3, return the name of the smallest box whose capacity is at least the litres needed in G2. In G4, return its price. If nothing is big enough, show Too big.
  3. Find by part of a name. In G3, return the price of the first item whose description contains the word typed in G2. If there is no match, show No match.

Questions people ask about this

Updated Checked by running: every exercise answer on this page was graded by our formula engine on .

Graded practice for this lesson is part of Pro

The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 43 Pro lessons, Excel and Google Sheets, 125 exercises in all.

  1. Latest cost
  2. Smallest box that fits
  3. Find by part of a name