Ordered ranges in MATCH() and VLOOKUP()

When you use MATCH() or VLOOKUP() in a ordered range, you can use the functions in a way that it finds a row even if there isn’t a perfect match of the search_key value.

VLOOKUP(,,,true) and MATCH(,,1)
The range is assumed to be sorted in an ascending order (small to big).

  • If search_key is a number or a date or a boolean (true or false), the system looks for the row whose first cell has the biggest number that is smaller-or-equal to the search_key. Text, hyperlinks, JSON cells that don’t include numbers are ignored.
  • If search_key is a text or an hyperlink or a JSON, then the system performs a lexicographical comparison. We will be looking for a row whose first cell has the lexicographically-biggest text that is lexicographically-smaller-or-equal to the search_key. We don’t ignore numbers. They’re still compared lexicographically.

MATCH(,,-1)
The range is assumed to be sorted in a descending order (big to small).

  • If search_key is a number or a date or a boolean (true or false), the system looks for the smallest number that is bigger-or-equal to the search_key. Text, hyperlinks, JSON cells that don’t include numbers are ignored.
  • If search_key is a text or an hyperlink or a JSON, then the system performs a lexicographical comparison. The system will look for the lexicographically-smallest text that is lexicographically-bigger-or-equal to the search_key. We don’t ignore numbers. They’re still compared lexicographically.

Now, what is a lexicographical comparison? It’s a useful operation when you want to compare texts. Is a>b? Is aa>a? What about "12">"2"? In all those operations, we must decide what is the biggest, to return TRUE or FALSE. This outcome of the comparison is determined by the lexicographical order, hence the name.

The lexicographical order results in the following ordered list (from smallest to largest):

  • 0
  • 1
  • 12
  • 2
  • 9
  • A
  • AA
  • B
  • C
  • Z
  • a
  • aa
  • b
  • z
  • zzz

Note the following interesting observations: “12” (the text) is smaller than “2”. “aa” is bigger than “a” but smaller than “b”. This is because the comparison is per-character so “2” is bigger than “12” and bigger than “111111111”. It’s also interesting to note that upper-case letters are smaller than lower-case.