Xlookup function

 

XLOOKUP function which is used to extract the cell value from any selected data source with reference to the selected lookup value. XLOOKUP works for the data either it is vertically or horizontally organized which means XLOOKUP has the functionally for both VLOOKUP & HLOOKUP function.

Syntax

There are 6 arguments used in XLOOKUP function out of which first 3 arguments are required and others are optional as follows.

1. lookup_value:  

Cell that contain text or number which is selected to search the same from the selected lookup-array and extract the desired value from the selected return_array.   

2. lookup-array:

Range of cells from the source data that is selected to search the selected lookup_value.

3. return_array

Range of cells from the source data that is selected from which the corresponding value will be returned, based on the position of lookup_value in the lookup_array.

4. [if_not_found] (optional)

This argument allows the user to enter text or number or any formula that is resulted if lookup_value does not exist in the lookup-array. If this argument is not used and lookup_value does not exist in the lookup-array then the result will be #N/A by fault. It must be noted that if we enter any text then it must be covered with double quotation marks (“ ”) otherwise the result will be name error (#NAME?).

5. [match_mode] (optional)

This argument has the 4 parameters as follows:

• 0 – Exact match:

This parameter enables the search of selected exact lookup_value. This is the default parameter.

• -1 – Exact match or next smaller item

This parameter enables the search of selected lookup_value. If not found, then searches the next smaller item.

• 1 – Exact match or next larger item

This parameter enables the search of selected lookup_value. If not found, then searches the next larger item.

• 2 – Wildcard character match

This parameter enables the search of entered lookup_value using special characters such as asterisk (*) either at the beginning or ending of the lookup_value as required. This parameter is applicable in case of text values only.

                  

6. [search_mode] (optional)

This argument has the 4 parameters as follows:

• 1 – Search first-to-last:

This parameter enables the search of lookup_value from the direction of top to bottom in selected lookup-array. This is the default parameter.

• -1 – Search last-to-first:

This parameter enables the search of lookup_value from the direction of bottom to top in selected lookup-array.

• 2 – Binary search (sorted ascending order):

This parameter works as same as parameter 1 with the condition that lookup-array must be sorted in ascending order.

• -2 – Binary search (sorted descending order):

This parameter works as same as parameter -1 with the condition that lookup-array must be sorted in descending order.

                         

 

 

For example:    We have the following requirements to be fulfilled with the help of Xlookup function.

1:           We have the data source for the resources assigned to the project # R-0020093772 as shown in the following screenshot of Table1.  Find out the ersonnel Number in reference to Portal ID provided in the following Table2.

        

              Solution:            Type the Xlookup in a cell I3 beginning with sign ‘=’ and press the keyword ‘Tab’ and fill the required arguments as follows:

1. lookup_value:    Select the cell H3, function will automatically consider the value of the selected cell.
2. lookup_array:    Select the range A2:A16 (having Portal ID) from Table1. Make sure to freeze the range with sign ‘$’ i.e $A$2:$A$16 so that when the function gets copied to other cell, range reference will still remain in position.
3. return-array:     Select the range B2:B16 (having Personnel Number) from Table1. Make sure to freeze the range with sign ‘$’ i.e $B$2:$B$16 so that when the function gets copied to other cell, range reference will still remain in position.
4. Press enter and copied the function into below 4 cells, the desired results will be displayed as shown in the following screenshot.

                 

                     

Now, as shown in the above screenshot, there is an error (#N/A) in one cell wherein the function has been applied. This error occurred as there is no Portal ID – 100000 exists in the selected lookup_array that is why function gives the result as #N/A by default.

Suppose, we want to put condition that in case of this error, the result should be ‘lookup_value does not exist in the lookup_array’ then we can use the 4^th optional argument [if_not_found] and modify the function to get the desired result as shown in the following screenshot.  

 

 

2:           With reference to Resource details provided in the following Table1, find out the Portal ID in reference to the First Name provided in Table2.            

    

                             

Solution:            Type the Xlookup in a cell M3 beginning with sign ‘=’ and press the keyword ‘Tab’ and fill the required arguments as follows:

1. lookup_value:    Select the cell L3, function will automatically consider the value of the cell. 
2. lookup_array:    Select the range C2:C15 (having Salary Slab) from Table1. Make sure to freeze the range with sign ‘$’ i.e $C$2:$C$15 so that when the function gets copied to other cell, range reference will still remain in position.
3. return-array:     Select the range A2:A15 (having Income Tax Slab Rates) from Table1. Make sure to freeze the range with sign ‘$’ i.e $A$2:$A$15 so that when the function gets copied to other cell, range reference will still remain in position.
4. [if_not_found]:  We can enter any text as per the user convenience. In this case, consider it as “Not Found”.
5. [match_mode]:  This argument is necessary to use in this case as we have first names of the resources selected as lookup_value however there are full names provided in the lookup_array. So, choose the parameter as 2– Wildcard character match and add the sign of asterisk (*) after the selected cell as lookup_value. With this combination of [match_mode] and lookup_value ending with asterisk (*), function becomes able to search the characters of the lookup_value from the lookup_array.
6. [search_mode]: By default, the function is applied from top to bottom so we can avoid this argument in this case. It is recommended to use in rare scenarios when the function needs to be applied with bottom to top configuration.
7. Press enter and copied the function into below 13 cells, the desired results will be displayed as shown in the following screenshot.

               

3:           With reference to Income Tax Slab Rates provided in the following Table1, find out the Tax Rate applicable in reference to the salaries provided in Table2.

                                           

Solution:            Type the Xlookup in a cell F3 beginning with sign ‘=’ and press the keyword ‘Tab’ and fill the required arguments as follows:

1. lookup_value:    Select the cell E3, function will automatically consider the value of the cell.
2. lookup_array:    Select the range A2:A9 (having Salary Slab) from Table1. Make sure to freeze the range with sign ‘$’ i.e $A$2:$A$9 so that when the function gets copied to other cell, range reference will still remain in position.
3. return-array:     Select the range B2:B9 (having Income Tax Slab Rates) from Table1. Make sure to freeze the range with sign ‘$’ i.e $B$2:$B$9 so that when the function gets copied to other cell, range reference will still remain in position.
4. [if_not_found]:  We can enter any text as per the user convenience. In this case, consider it as “Not Found”.
5. [match_mode]:  We need to use this argument in this case because as you can understand the data, Income Tax rates are provided in reference to range of salaries which means from 0 to 249,999 INR, tax rate should be 0% and from 250,000 to 499,999, rate should be 5%, so on. For this kind of data, [match_mode] is useful and necessary to apply.
6. As per the data, tax slabs are applicable on salary at the increasing rate. Hence, use the parameter as 1 (Exact match or next larger item) to get the correct result.
7. [search_mode]: By default, the function is applied from top to bottom so we can avoid this argument in this case.
8. Press enter and copied the function into below 6 cells, the desired results will be displayed as shown in the following screenshot.    

              

If you have any kind of challenge or you want to learn about any other function/option which is not covered or you want me to add more into the previously covered topic, please comment. I will be happy to answer your queries or take your suggestions.