Jump to content
One on One Extensive Advanced Excel Training ×
Business Intelligence & Analytics for Digital Transformation

Welcome XLOOKUP, the successor of the most ever powerful VLOOKUP


admin

Recommended Posts

XLOOKUP is currently a beta feature, and only available to a portion of Office Insiders at this time. Will continue to optimize it over the next several months. When XLOOKUP is ready, it will be released to all Office Insiders, and Office 365 subscribers.

xlookup.thumb.png.ca17aa2640ad862000458eb09ec0aee7.png

The XLOOKUP function searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. 

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

Example 1

This example is from the video above, and uses a simple XLOOKUP to look up a country name, then return its telephone country code. It only includes the lookup_value (cell F2), lookup_array (range B2:B11), and return_array (range D2:D11) arguments. It does not include the match_mode argument, as XLOOKUP defaults to an exact match.

1196650839_example1.jpg.4efccd75d6c77de15fd42ec2d8f7df27.jpg

Note: XLOOKUP is different from VLOOKUP in that it uses separate lookup and return arrays, where VLOOKUP uses a single table array followed by a column index number. The equivalent VLOOKUP formula in this case would be: =VLOOKUP(F2,B2:D11,3,FALSE)

 

Example 2

In this example, we're looking up employee information based on an employee ID number. Unlike VLOOKUP, XLOOKUP is able to return an array with multiple items, which allows a single formula to return both employee name and department (cells C5:D14).

253524567_example2.jpg.db579f6892a27928b6b1fe2adc1e2365.jpg

 

Example 3

This example adds the if_not_found argument to the example above.

1587374338_example3.jpg.efe75f430b9117b1ef4ea5dbcb6d5365.jpg

 

Example 4

The following example looks in column C for the personal income entered in cell E2, and finds a matching tax rate in column B. It uses the match_mode argument set to 1, which means the function will look for an exact match, and if it can't find one, it will return the next larger item.

1483154767_example4.jpg.7352364428779a0506349cd15c2d942b.jpg

 

Note: Unlike VLOOKUP, the lookup_array column is to the right of the return_array column, where VLOOKUP can only look from left-to-right.

 

Example 5

Next, we'll use a nested XLOOKUP function to perform both a vertical and horizontal match. In this case, it will first look for Gross Profit in column B, then look for Qtr1 in the top row of the table (range C5:F5), and return the value at the intersection of the two. This is similar to using the INDEX and MATCH functions in conjunction. You can also use XLOOKUP to replace the HLOOKUP function.

664083175_example5.jpg.26509f15eef269083ceb63cc3e18d3f8.jpg

The formula in cells D3:F3 is: =XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17)).

 

Example 6

This example uses the SUM function, and two XLOOKUP functions nested together to sum all the values between two ranges. In this case, we want to sum the values for grapes, bananas, and include pears, which are between the two.

270352501_example6.jpg.e6f15363df5fd052c99f8618bc5752d2.jpg

 

The formula in cell E3 is: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))

How does it work? XLOOKUP returns a range, so when it calculates, the formula ends up looking like this: =SUM($E$7:$E$9). You can see how this works on your own by selecting a cell with an XLOOKUP formula similar to this one, then go to Formulas > Formula Auditing > Evaluate Formula, and press the Evaluate button to step through the calculation.

 

Link to comment
Share on other sites

  • 5 months later...

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...