Searching Left and From the Bottom

One of VLOOKUP's biggest limits was that it could only return values to the right of the lookup column. XLOOKUP has no such rule.

Because you supply the lookup array and return array separately, the answer can be anywhere - left, right, above, or below the column you search.

Suppose IDs are in column B and names are in column A, to the left. VLOOKUP could not do this without rearranging columns.

With XLOOKUP you simply search column B and return column A. The function happily returns a value that sits left of the lookup column.

XLOOKUP has an optional fifth argument, search_mode, that controls the direction of the search.

• 1 - search first to last (the default)
• -1 - search last to first
• 2 - binary search on ascending data
• -2 - binary search on descending data

By default XLOOKUP searches from the top down, returning the first match it encounters.

If a value appears several times in your list, this default gives you the earliest one. That is fine most of the time, but sometimes you want the most recent entry instead.

Set search_mode to -1 to search from the bottom up. XLOOKUP then returns the last matching value.

Because if_not_found and match_mode come before search_mode, you must include the earlier arguments as placeholders, even if they are empty.

Imagine a log where each row records a status update for an order, oldest at the top.

Searching top-down gives the order's first status. Searching bottom-up with -1 gives its latest status. The same data, two very different answers depending on direction.

Arguments are positional, so you cannot skip to the sixth one without naming the ones before it.

In =XLOOKUP(D2, A2:A20, B2:B20, "Not found", 0, -1) the "Not found" is if_not_found and the 0 is match_mode (exact match). These hold the slots so -1 lands in search_mode.

The fifth argument's neighbor, match_mode, controls how matching works:

• 0 - exact match (default)
• -1 - exact or next smaller
• 1 - exact or next larger
• 2 - wildcard match using * and ?

For left and bottom searches you usually keep this at 0.

A price-change sheet lists each product every time its price updates. To get the current price you want the last entry for that product.

Searching from the bottom with -1 returns the most recent price without sorting or extra helper columns.

Search modes 2 and -2 use a faster binary search, but they require the lookup array to be sorted - ascending for 2, descending for -2.

On huge sorted lists this is much quicker. If the data is not truly sorted, binary search can return wrong results, so use it carefully.

You now control both where and which way XLOOKUP searches:

• Return values from the left freely
• 1 for first match (default)
• -1 for last match
• 2 or -2 for fast binary search on sorted data

Remember to fill the if_not_found and match_mode slots before search_mode.

Test your understanding of the search_mode argument.

You learned XLOOKUP's directional power:

• Return arrays can sit to the left of the lookup array
• search_mode (the 6th argument) sets direction
• -1 finds the last match, ideal for latest-record lookups
• 2 and -2 enable fast binary search on sorted data

Next you will return entire rows or columns from a single XLOOKUP.