Quick Links
The XLOOKUP function in Google Sheets gives you an easy way to find the data you’re looking for quickly. XLOOKUP doesn’t have the same limitations as VLOOKUP and HLOOKUP, enabling you to perform lookups in any direction.
If you’rea Microsoft Excel user, you may haveused XLOOKUP there. Luckily, it works the same way in Google Sheets. Whether you’re used to the function in Excel or brand new to it altogether, we’ll show you how to use XLOOKUP to find specific values from a range of cells.
About XLOOKUP in Google Sheets
With the XLOOKUP function and accompanying formula, you canperform a lookupin one cell range and return a matching result from another. This is handy for sheets that contain a lot of data where using your eyeballs is time-consuming.
Related:How to Use INDEX and MATCH in Google Sheets
The syntax for the function is
The first three arguments are required. The three remaining arguments can be used to customize your lookup.
How to Use XLOOKUP in Google Sheets
To show how the function works, we’ll start with asimple lookupusing the required arguments and then move on to additional examples that use the optional arguments.
Related:How to Find Data in Google Sheets with VLOOKUP
Here, we have a sheet of customer orders that includes contact details and order information. For the first example, we’ll do a simple lookup of the Order Number to return the Customer Name using this formula:
To break down the formula, 1234356 is thesearch_valueor order number, D2:D14 is thelookup_range, and A2:A14 is theresult_range. As you can see, Order Number 123456 belongs to Marge Simpson.
Because XLOOKUP can work from left to right as well as right to left, we can do the reverse. Here, we’ll look up Marge Simpson in the range A2 through A14 to find her Order Number in the range D2 through D14.
Unlike VLOOKUP which works vertically and HLOOKUP which works horizontally, XLOOKUP works in both directions.
Missing Value
In this next example, we’ll include “ZERO” for themissing_value. So, if our search_value isn’t found, we’ll see ZERO instead of the default #N/A.
Because our lookup of Homer Simpson isn’t found in the range A2 through A14, our result is ZERO.
Match Mode
For an example using thematch_modeargument, we’ll use asearch_valueof 29 for the Amount in the range F2 through F14 to find the Customer Name in the range A2 through A14.
We’ll include amatch_modeof 1 for an exact match or the next greater value. Note that there’s nomissing_valueargument in the formula.
You can see the result is Raj Koothrappali. Because there’s no match for 29, the formula gives us a result for the next higher value which is 30.
Search Mode
Here’s one more example using both thematch_modeandsearch_modearguments with the samesearch_valueof 29 in F2 through F14. Again, we look for the Customer Name in the range A2 through A14.
We’ll look for an exact match or the next lower value by searching from the last entry to the first. So, we enter -1 for thematch_modeand -1 for thesearch_mode. Like above, themissing_valueis omitted.
As you can see, the result is Michael Kelso. Because there’s no match for 29, the formula gives us the next lower value which is 28. Even though Eric Forman also matches with 28, we performed the search from the last entry to the first (bottom to top), so Michael Kelso is the first result found.
If we were to search from the first entry to the last (top to bottom) using asearch_modeof 1 instead of -1, then Eric Forman would be the result found.
When you have a spreadsheet full of data, looking up a value tofind its matching datacan take time. But if you use XLOOKUP in Google Sheets, you’ll find what you need in a snap.
For more, check outthese basic Google Sheets functionsyou might want to try.
