The Function of XLookUp

XLOOKUP has the function of looking for things in a range or table by row.

As an example, you can search for an employee’s name by searching for their employee ID.

You may also search for a car part based on its part number. XLOOKUP can help you search for a search term in a column and have a result returned from another column’s same row, whatever side the return column may be.

Syntax

The function of XLOOKUP is that it looks for an array or range.

It has the corresponding item to the first match that it finds returned.

Whenever a match doesn’t exist, XLOOKUP can provide the approximate or closest equivalent.

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

ARGUMENT DESCRIPTION
lookup_array Required The range or array to search
return_array Required The range or array to return
[if_not_found]
Optional 
If there is no good match found, you will return the [if_not_found] text you supply.
 Whenever a good match is not found, and the [if_not_found] is missing, it will return #N/A.
[match_mode]
Optional  Specify the match type:
0 – The exact default match.  If you can’t find anything, return #N/A.
 -1 – Exact match. If you can’t find anything, have the smaller item returned.
1 – Exact match.  If you can’t find anything, have the next more prominent item returned.
2 – The wildcard match where ~, ?, and * have a special meaning.
[search_mode] Have the search mode to use specified:
Optional  1 – The default to perform a search that begins at the first item.
-1 – The performance of a reverse search that begins at the last item.
2 – The performance of a binary search relies on the sorting of a lookup_array in ascending order. If it doesn’t get sorted, it will return invalid results.
-2 – Have a binary search performed that is reliant on the lookup_array sorted in descending order. If it doesn’t get sorted, it will return invalid results.

Examples of XLookUp

Example 1    

You can look up employee information by using the employee ID number.

It is not like VLOOKUP as XLOOKUP can return multiple items in an array that a single formula will replace both the department and employee name from cells C5:D14.

Example 2    

With a couple of nested XLOOKUP functions and the SUM function, you can have all the values summed up between a couple of ranges.

For this, if we want to have the values for bananas, pears, and grapes summed up and which could be between the two, this will be the formula in the E3 cell:

=SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))

How can this work?

The XLOOKUP will return a range that calculating it will have a formula that would end up appearing like this:

=SUM($E$7:$E$9)

On your own, you can see how it works when you choose a cell with an XLOOKUP formula like this.

Then, choose Formulas > Formula Auditing > Evaluate Formula.

After that, select Evaluate so you can go through with the calculation.

———————————————————————————

Example 3

You can use XLOOKUP to search for the name of a country in range and have its telephone country code returned.

Included here are the arguments return_array (range D2:D11), lookup_value (cell F2), and lookup_array (range B2:B11).

Here, the match_mode argument is not included as the XLOOKUP can produce the exact match as default.

Note: Return array and lookup array are what XLOOKUP uses, while a single table array followed by the column index number is what the VLOOKUP uses.

In this case, the VLOOKUP formula’s equivalent would be: =VLOOKUP(F2,B2:D11,3,FALSE)

———————————————————————————

Example 4

    

The use of the nested XLOOKUP function is possible for both a horizontal and vertical match.

First, it searches for the Gross Profit in column B while it checks out the top row of the table’s Qtr1 on the range C5:F5.

At the two’s intersection, it will finally return the value that is the same as using the MATCH and INDEX functions together.

Tip: You can also have XLOOKUP used for the replacement of the function HLOOKUP.

Note: For the cells D3:F3, this is the formula: =XLOOKUP (D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17)).

———————————————————————————

Example 5   

The preceding example includes the argument if_not_found.

———————————————————————————

Example 6   

Here, it looks at column C for the personal income entered in the E2 cell and searches for the matching tax rate in the B column.

It sets the argument if_not_found to have it returned to zero (0) when nothing is found there. The argument match_mode can get set to 1.

It means that the function will search for a precise match. If it is not able to find any, it will have the next more oversized item returned.

The argument search_mode will finally get set to 1. It means that the function will look from the first to the last thing.

Take note that the column lookup_array of XARRAY is at the right of the column return_array, while you can only look at VLOOKUP from left to right.

Know More About Excel with ExcelMaster

Allow yourself to become knowledgeable about Microsoft Excel’s XLookUp and other Excel functions with ExcelMaster NOW!

Leave a Comment