The XLOOKUP operate in Google Sheets provides you a simple approach to discover the info you’re searching for shortly. XLOOKUP doesn’t have the identical limitations as VLOOKUP and HLOOKUP, enabling you to carry out lookups in any course.
If you happen to’re a Microsoft Excel consumer, you will have used XLOOKUP there. Fortunately, it really works the identical manner in Google Sheets. Whether or not you’re used to the operate in Excel or model new to it altogether, we’ll present you how you can use XLOOKUP to search out particular values from a variety of cells.
About XLOOKUP in Google Sheets
With the XLOOKUP operate and accompanying components, you may carry out a lookup in a single cell vary and return an identical end result from one other. That is helpful for sheets that include a variety of information the place utilizing your eyeballs is time-consuming.
The syntax for the operate is XLOOKUP(search_value, lookup_range, result_range, missing_value, match_mode, search_mode).The primary three arguments are required. The three remaining arguments can be utilized to customise your lookup.
- Search_value: The worth to lookup which is usually a quantity, textual content, or cell reference. Textual content must be positioned inside citation marks.
- Lookup_range: The cell vary to search for the
search_valuewhich must be a single row or column. - Result_range: The cell vary to search for the end result that corresponds to the
search_valuewhich must be the identical measurement because the lookup_range. - Missing_value: The worth to return if there’s no match to the
search_value. The components returns the #N/A error by default. - Match_mode: The way to discover the matching
search_value. Enter 0 for a precise match, 1 for a precise match or the following worth larger than thesearch_value, -1 for a precise match or subsequent worth lower than thesearch_value, or 2 for a wildcard match. The default is 0. - Search_mode: The way to search the
lookup_range. Enter 1 to look from the primary to the final entry, -1 to look from the final to the primary entry, 2 to make use of a binary search with values in ascending order, or -2 to make use of a binary search with values in descending order. The default is 1.
The way to Use XLOOKUP in Google Sheets
To indicate how the operate works, we’ll begin with a easy lookup utilizing the required arguments after which transfer on to extra examples that use the elective arguments.
RELATED: The way to Discover Knowledge in Google Sheets with VLOOKUP
Right here, we’ve a sheet of buyer orders that features contact particulars and order info. For the primary instance, we’ll do a easy lookup of the Order Quantity to return the Buyer Title utilizing this components:
=XLOOKUP(123456,D2:D14,A2:A14)
To interrupt down the components, 1234356 is the search_value or order quantity, D2:D14 is the lookup_range, and A2:A14 is the result_range. As you may see, Order Quantity 123456 belongs to Marge Simpson.
As a result of XLOOKUP can work from left to proper in addition to proper to left, we will do the reverse. Right here, we’ll lookup Marge Simpson within the vary A2 by A14 to search out her Order Quantity within the vary D2 by D14.
=XLOOKUP("Marge Simpson",A2:A14,D2:D14)
Word: Not like VLOOKUP which works vertically and HLOOKUP which works horizontally, XLOOKUP works in each instructions.
Lacking Worth
On this subsequent instance, we’ll embrace “ZERO” for the missing_value. So, if our search_value isn’t discovered, we’ll see ZERO as a substitute of the default #N/A.
=XLOOKUP("Homer Simpson",A2:A14,D2:D14,"ZERO")
As a result of our lookup of Homer Simpson isn’t discovered within the vary A2 by A14, our result’s ZERO.
Match Mode
For an instance utilizing the match_mode argument, we’ll use a search_value of 29 for the Quantity within the vary F2 by F14 to search out the Buyer Title within the vary A2 by A14.
We’ll embrace a match_mode of 1 for a precise match or the following larger worth. Word that there’s no missing_value argument within the components.
=XLOOKUP(29,F2:F14,A2:A14,,1)
You may see the result’s Raj Koothrappali. As a result of there’s no match for 29, the components provides us a end result for the following larger worth which is 30.
Search Mode
Right here’s yet one more instance utilizing each the match_mode and search_mode arguments with the identical search_value of 29 in F2 by F14. Once more, we search for the Buyer Title within the vary A2 by A14.
We’ll search for a precise match or the following decrease worth by looking out from the final entry to the primary. So, we enter -1 for the match_mode and -1 for the search_mode. Like above, the missing_value is omitted.
=XLOOKUP(29,F2:F14,A2:A14,,-1,-1)
As you may see, the result’s Michael Kelso. As a result of there’s no match for 29, the components provides us the following decrease worth which is 28. Despite the fact that Eric Forman additionally matches with 28, we carried out the search from the final entry to the first (backside to prime), so Michael Kelso is the primary end result discovered.
If we have been to look from the first entry to the final (prime to backside) utilizing a search_mode of 1 as a substitute of -1, then Eric Forman can be the end result discovered.
When you’ve gotten a spreadsheet full of information, trying up a worth to discover its matching information can take time. However for those who use XLOOKUP in Google Sheets, you’ll discover what you want in a snap.
For extra, take a look at these fundamental Google Sheets features you would possibly need to strive.
