How to Use XLOOKUP in Google Sheets

The XLOOKUP function is the most powerful and flexible spreadsheet lookup function. In this tutorial, you’ll learn how to use the XLOOKUP function in Google Sheets with 7 examples, from simple to complex. We’ll look at how to return values to the left of the search column, how to do approximate matching, how to use the built-in error handling, do wildcard matching, and much more.
📚 Additional resources:
1. Template to follow along: docs.google.com/spreadsheets/...
2. XLOOKUP written tutorial: www.benlcollins.com/spreadshe...
📖 Chapters:
0:00 - Introduction
0:43 - Example 1: Basic XLOOKUP
2:43 - Example 2: Error handling
4:15 - Example 3: Lookup to the left
4:57 - Example 4: Approximate matching
7:21 - Example 5: Wildcard matching
10:58 - Example 6: Returning multiple results
11:45 - Example 7: Search options (top to bottom or bottom to top)
✉️ Google Sheets Tips Newsletter, my free weekly newsletter:
www.benlcollins.com/google-sh...
#googlesheets #googlesheetstutorial #spreadsheet

[@godzilloff]

Ben, thank you very much for the very useful content. A very necessary and timely video. Happy New Year!

[@benlcollins]

You're welcome! Happy New Year to you too!

[@patricialee6265]

Hi Ben, this is great, but could you show how to use this to lookup values in another Tab sheet within the Google Sheet? Thank you.

[@SheetsNinja]

So you can use xlookup to reference another tab within the same sheet as follows:
=xlookup(A1,Tab1!C:C,Tab1!D:D)
Or you can refer to an entirely different spreadsheet by using importrange like this:
=xlookup(A1,importrange("spreadsheet_url","Tab1!C:C"),importrange("spreadsheet_url","Tab1!D:D"))

[@rb8058]

Helpful, thank you! Can XLOOKUP incorporate what before would be an array formula/Vlookup

[@benlcollins]

No, it still only accepts a single search key (e.g. E4) on its own. To use a range of search keys, you still have to wrap it with the ArrayFormula e.g. =ArrayFormula(XLOOKUP(E4:E6,A4:A13,C4:C13))

[@laurienason1310]

Ben, how does the XLOOKUP function play with the ARRAYFORMULA function? And in terms of speed over large recordsets, would VLOOKUP be faster?

[@benlcollins]

It works with array formulas. The XLOOKUP on its own only accepts a single search key (e.g. E4). To lookup a range, you have to wrap it with the ArrayFormula e.g. =ArrayFormula(XLOOKUP(E4:E6,A4:A13,C4:C13))
Don't know how the speed compares to a VLOOKUP on big datasets, but that might be a topic for a future video.

[@Clipsbykim2]

thanks 12:40

[@helmanfrow]

1:55 If you've ever created a Google sheet with more than 50,000 of rows you'll know why this feature is hilarious.

[@benlcollins]

I live in hope that it'll get better for larger datasets! ;)