How to Use XLookup in Excel

Trending 3 months ago

Working pinch ample information sets is ever tricky. Excel is 1 of nan astir powerful devices you tin usage to manipulate, format, and analyse ample sets of information.

Person utilizing xlookup successful excel

Several well-known functions beryllium to thief you look up circumstantial matches wrong a dataset, for illustration VLookup.

But functions for illustration VLookup had definite limitations, truthful Microsoft precocious introduced nan XLookup usability to Excel. It provides a much robust method for uncovering circumstantial values successful a information set.

Knowing really to usage it efficaciously tin drastically trim down nan clip you walk trying to analyse information successful Excel.

What does XLookup do successful Excel?

The Benefits of XLookup successful Excel

XLookup Required Arguments

XLookup Optional Arguments

How to Use XLookup successful Excel

Best Practices for XLookup successful Excel

 Download 10 Excel Templates for Marketers [Free Kit]

What does XLookup do successful Excel?

The halfway functionality of nan XLookup is nan expertise to hunt for circumstantial values successful a scope aliases array of data. When you tally nan function, a corresponding worth is returned from different scope aliases array.

Ultimately, it’s a hunt tool for your Excel datasets.

Depending connected your arguments, XLookup will show you nan first aliases past worth successful instances pinch much than 1 matching result.

Other lookup functions for illustration VLookup (vertical lookup) aliases HLookup (horizontal lookup) person limitations, specified arsenic only searching from correct to left. Users request to either rearrange information aliases find analyzable workarounds to mitigate this.

XLookup is simply a acold much elastic solution, allowing you to get partial matches, usage much than 1 hunt criteria and usage nested queries.

Check retired this XLookup Excel tutorial to spot it successful action:

The Benefits of XLookup successful Excel

The main use of XLookup successful Excel is nan clip savings it provides. You tin execute nan hunt consequence without manipulating information positioning, for example.

Like different Excel lookup formulas, it saves a immense magnitude of clip that would different person to beryllium spent manually scrolling done rows and rows of data.

When it comes to comparisons pinch different lookup functions, XLookup’s elasticity is key. For example, you tin usage wildcard characters to hunt for partial matches.

This helps to trim errors overall, arsenic you tin usage it to find information that whitethorn beryllium misspelled aliases entered incorrectly.

The arguments for XLookup are besides simpler than VLookup, making it faster and easier to use. XLookup defaults to an nonstop match, for example, whereas you would person to specify this successful your VLookup argument.

The wide functionality of XLookup is superior since it tin return aggregate results astatine once. One illustration of this usage lawsuit would beryllium searching for nan apical 5 values successful a peculiar dataset.

XLookup Required Arguments

Your XLookup usability requires 3 arguments:

  • Lookup_value: This is nan worth you’re searching for successful an array.
  • Lookup_array: This is nan scope of cells wherever you want nan return worth to beryllium displayed.
  • Return_array: This is nan scope of cells wherever you want nan usability to hunt for nan value.

So a elemental type of an XLookup would look for illustration this:

=XLOOKUP(lookup_value, lookup_array, return_array)

XLookup Optional Arguments

The XLookup usability besides has respective optional arguments that you tin usage for much analyzable scenarios aliases to constrictive down your search:

  • Match_mode: This determines nan type of lucifer to use, specified arsenic nonstop lucifer aliases wildcard lucifer to get partial matches.
  • Search_mode: This determines whether your usability should hunt from near to correct aliases vice versa.
  • If_not_found: This statement tells nan usability what worth should beryllium returned if location is nary lucifer astatine all.

With optional arguments included, nan XLookup usability looks for illustration this:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

How to Use XLookup successful Excel

1. Open nan Excel and nan datasheet connected which you want to usage nan XLookup function.

2. Select nan compartment wherever you want to spot nan XLookup Excel formula.

3. Type “=” into that compartment and past type “XLookup.” Click connected nan “XLookup” action that comes up successful nan dropdown.

 xlookup example, Excel

4. An opening parenthesis will automatically generate. After nan opening bracket, participate nan required arguments successful nan correct order: lookup_value, lookup_array, and return_array.

This intends selecting nan scope of cells for each statement and placing a comma earlier moving connected to nan adjacent argument.

xlookup example, Excel

5. If you’re utilizing immoderate optional arguments, participate them aft nan required arguments.

6. When your XLookup arguments are complete, adhd a closing parenthesis earlier hitting enter.

7. The results of your XLookup should beryllium displayed successful nan compartment wherever you entered nan function.

xlookup example, ExcelIn this Excel XLookup example, we utilized XLookup to find retired nan people for a peculiar student. So, nan lookup_value was nan student's sanction (“Ruben Pugh”).

The lookup_array was nan database of student names nether file A. Finally, nan lookup_return was nan database of student grades nether file C. With that formula, nan usability returned nan worth of nan student’s grade: C-.

But really do you usage XLookup pinch nan optional arguments?

Using nan aforesaid information group arsenic above, let’s opportunity we want to find nan attendance complaint for a student pinch nan surname “Smith.”

Here, we’re telling Excel to return nan connection “Not Found” if it cannot retrieve nan value, indicating that nan student “Smith” is not connected this list:

xlookup example, ExcelNow, let’s opportunity we want to find whether immoderate students had an attendance complaint of 70%. But we besides want to cognize nan adjacent closest attendance complaint if 70% does not beryllium successful nan dataset.

We’ll usage nan worth “1” nether nan match_mode statement to find nan adjacent largest point if 70 does not exist:

xlookup example, ExcelSince nary student has a 70% attendance rate, nan XLookup has returned “75,” nan adjacent largest value.

Best Practices for XLookup successful Excel

Use Descriptive References

It’s preferable to usage descriptive references for nan cells you’re utilizing alternatively than generic ranges for illustration A1:A12.

When it comes clip to set your look (which you thin to do often pinch a usability for illustration XLookup), descriptive references make it easier to understand what you were primitively utilizing nan look to do.

This is besides useful if you walk nan spreadsheet disconnected to a caller personification who needs to understand nan look references quickly.

Use Exact Match Where Possible

Using nan nonstop lucifer default wrong nan look (as opposed to wildcard characters) helps guarantee you don’t get unintended values successful nan return.

Wildcard characters are much useful for identifying partial matches, but nan nonstop lucifer is nan champion measurement to guarantee nan look useful arsenic intended.

Keep Argument Ranges nan Same Size

When putting your arguments together, make judge that you usage nan aforesaid number of cells for nan return_array and nan look_up array. If not, you’ll get an error, and Excel will only return #VALUE successful nan cell.

Test Your Formula

It’s not difficult to hole problems pinch a elemental XLookup. But if you’re utilizing nested functions aliases XLookup successful conjunction pinch different formulas, guarantee you’re testing everything on nan way.

If you don’t and person an error, it tin beryllium challenging to activity backward and place wherever nan usability is going wrong.

Getting Started

As a caller and improved measurement to usage lookup functionality successful Excel, nan XLookup outperforms nan classical VLookup successful aggregate ways.

While nan basics of nan usability are easy to grasp, it tin return immoderate believe to usage XLookup successful much analyzable ways. But pinch immoderate believe information and trial scenarios to activity on, you’ll maestro nan XLookup successful nary time.

excel trading templates

We're committed to your privacy. HubSpot uses nan accusation you supply to america to interaction you astir our applicable content, products, and services. You whitethorn unsubscribe from these communications astatine immoderate time. For much information, cheque retired our Privacy Policy.

Tired of struggling pinch spreadsheets? These free Microsoft Excel templates tin help.

Source Marketing