How to use XLOOKUP in Google Sheets

How to Use XLOOKUP in Google Sheets
How to search in Google Sheets (Image credit: Shutterstock)

Google Sheets is basically a calculator on steroids. It lets you process both text and number data in an infinite number of ways. Among the best tools of Google Sheets is the XLOOKUP, which helps you find values in your tables in an instant. XLOOKUP works by searching your data set and returning a value from the same row but in a different range. It’s the perfect tool for sifting through hundreds of rows for specific items or a range of items. There are a number of ways this could help you avoid manual data searches, but think of it as a cataloging tool. A used car dealer, for example, has all of their different models of cars and their corresponding prices in one Sheet. With XLOOKUP, they can automatically narrow down a range of cars within a specified price range. Curious about how you can incorporate XLOOKUP into your own productivity routine? Read on!

Using Google Sheet’s XLOOKUP function

1. Open your Google Sheet with your table array and select the cell you will put the function in. 

(Image credit: Laptop Mag)

2. Next, type in your function in the following format: =XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])

(Image credit: Laptop Mag)

Search_key: the value that will be used to search your table array. You can also enter another cell to make your search key dynamic.

Lookup_range: this is the column or range where your search key will be compared to.

Result_range: this is the column where the result value will come from.

(Image credit: Laptop Mag)

Missing_value (optional): this is the value that Google Sheets will return if it does not find a match for your search key.

Match_mode (optional): sets the level of accuracy of the search

  • Set 0 to search for an exact match only.
  • Set -1 to search for the next smaller item if there is no match.
  • Set 1 to search for the next larger item if there is no match.
  • Set 2 to use wildcards.

Search_mode (optional): sets the direction of the search.

  • Set 1 to search from the top. 
  • Set -1 to search from the bottom.
Read more
chatgpt search feature
There are now 3 great reasons to switch from Google Search to ChatGPT Search
An illustration of a man's silhouette and the top of his head is open like a lid as a slew of app icons float out into the air. The illustration is on a flat orange background.
I found an app that connects all my apps — without trying to replace them. It's a life-changer.
Microsoft Office
Microsoft's recent tests could lure Google Workspace users back to Office apps
The Acer Chromebook Plus Enterprise 515 playing a movie trailer, on a black desk against a white background.
Turn your Chromebook into a business laptop, for free
ChatGPT on a phone
This free ChatGPT feature could make Siri feel outdated
Samsung Dex
I replaced my laptop with Samsung Dex. Here’s what happened.
Latest in Work & Collaboration Tools
An illustration of a man's silhouette and the top of his head is open like a lid as a slew of app icons float out into the air. The illustration is on a flat orange background.
I found an app that connects all my apps — without trying to replace them. It's a life-changer.
Thunderbolt Share depiction
Intel Thunderbolt Share: The powerful PC-to-PC connection software is frustratingly elusive, but you don't need a new laptop to use it
The 39.7-inch Dell UltraSharp monitor beside a Laptop Mag deals icon in front of an orange and yellow background
Top 5 Dell UltraSharp monitor deals: Save up to $300 on the ultimate productivity monitor!
The Logitech MX Creative Console seen from above on a white table
Logitech MX Creative Console review: Is this alternative stream deck a creative game changer?
Google Search
Google Reverse Image Search: How to search with an image in Google
docs in proton drive
What is Proton Docs? Why you might want to give the privacy-focused Google competitor a chance
Latest in How-To
illustration of laptop with tower speakers on either side.
How I turned my office into a personal concert hall with tower speakers and a MacBook
Acer Predator Triton Neo 16 gaming and content creation laptop
3 critical tips and tricks for maximizing performance on your gaming laptop
Samsung Galaxy Chromebook Plus
How to restart a Chromebook
how to use copilot voice
Copilot Voice: Here's how you can chat with Microsoft's new AI companion
Screenshots of iPhone mirroring using iOS 18 and macOS Sequoia
iPhone Mirroring changed how I use my MacBook and iPhone — here's how it works
The iPhone 16's camera control button
How to use the iPhone 16's new Camera Control button to become a phone photography pro