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.
2. Next, type in your function in the following format: =XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])
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.
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.
