If you’rea Microsoft Excel user, you may haveused XLOOKUP there.
Luckily, it works the same way in Google Sheets.
This is handy for sheets that contain a lot of data where using your eyeballs is time-consuming.
The three remaining arguments can be used to customize your lookup.
As you’ve got the option to see, Order Number 123456 belongs to Marge Simpson.
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.
We’ll include amatch_modeof 1 for an exact match or the next greater value.
Note that there’s nomissing_valueargument in the formula.
it’s possible for you to see the result is Raj Koothrappali.
Again, we look for the Customer Name in the range A2 through A14.
So, we enter -1 for thematch_modeand -1 for thesearch_mode.
Like above, themissing_valueis omitted.
As you’re free to see, the result is Michael Kelso.
Because there’s no match for 29, the formula gives us the next lower value which is 28.
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.
