A1.2 XLOOKUP and XMATCH

XLOOKUP Description

XLOOKUP is a newer version of VLOOKUP. It can perform all of the functions allowed by VLOOKUP, but it also has more options and is much more flexible. In VLOOKUP we were constrained to a single table or range of values which would contain the first column with the lookup cells and a separate, designated column of cells for returned values. Also in VLOOKUP the default match was an approximate (closest smaller value) match, which worked best for numeric data ranges.

XLOOKUP has several new options and features that provide quite a bit more power. First, there is one table range for the lookup values and a completely different table range for the returned values. Obviously, the number rows of the two table ranges needs to be the same. However, the returned table range can have multiple columns, in which case, cells from the multiple columns will be returned. XLOOKUP also has several different methods for finding the matching cells. It also has an option to specify the method of searching the data for a match. Let's look at the syntax and the parameters.

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

• lookup_value - This can either be a literal value (string or numeric) or a cell reference.

• lookup_array - This is normally a single column of data to be searched for the match. It must be compatible with the lookup_value. Depending on the search_mode, it may need to be sorted data.

• return_array - This is the array of values to be returned. It should have the same number of rows as the lookup_array because it returns values from the same row number as the matched row in the lookup_array. If it has multiple columns, then the cell values from multiple columns will be returned.

• if_not_found - This optional parameter should normally contain a message (literal string or number) to display if there is no match found. If this value is omitted, then the default "#NA" message is displayed when there is no match.

• match_mode - This parameter indicates if the match needs to be an exact match or an approximate match. The default is to perform an exact match. Here are the values for the different options:

  • 0 - exact match. This is the default. It returns #NA or if_not_found message for no match

  • -1 - try exact match, but if none, then return the closest smaller item. (Similar to VLOOKUP default)

  • 1 - try exact match, but if none, then return the next larger item. (No equivalent operation in VLOOKUP)

  • 2 - do a wildcard match (*, ?, ~ ). "*" matches on multiple characters. "?" matches on a single character. "~" followed by a "*" or "?" matches on the literal asterisk or question mark.

• search_mode - Use this to make the search more efficient.

  • 1 - Search starting with row 1, i.e., the first item in the array. This is the default.

  • -1 - Search starting with the last row, i.e., the last item in the array.

  • 2 - Use a binary search. The lookup_array must be sorted in ascending order. Use for large table searches.

  • -2 - Use a binary search. The lookup_array must be sorted in descending order. Use for large table searches.

Examples of XLOOKUP

In our first example, let's use the same example that was used in Chapter 2 in Figure 2.1. In this example, we are first looking up the commission percentage for each sales person based on their sales values. The sales ranges and commission percentages are given in Columns A and B. However, in this example, the two columns are reversed from the previous example to illustrate the independence of the lookup_array and the return_array. We have also added the if_not_found value of "Not found" message. Notice the Match_mode value is -1 to indicate to first try an exact match, and then a closest smaller match. Finally, we have added an IF statement on the commission calculation to return a zero for those rows with a "not found" message.

For the next example, let's assume we have a list of employee time card entries. The entries have employee id and hours worked during a given week. We would like to add the employee name and the department in which they work. There is a master list of employees with id, name, department, and hire date. We will use the EmployeeID as the lookup_array (Column A), and the EmployeeName and Department as the return_array (Columns B and C).

XMATCH Description

The XMATCH function is a newer version of MATCH which searches for a specified item in an array or range of cells, and then returns the item's relative position. It searches a single dimension array as either a row or a column. When searching a column, it returns the row number, and when searching a row, it returns the column number. It can search either forward or backward in the lookup_array. The allowable options are similar to XLOOKUP with a syntax as follows:

XMATCH(lookup_value, looklup_array, [match_mode], [search_mode])

• lookup_value - This can either be a literal value (string or numeric) or a cell reference.

• lookup_array - This is normally a single column or row of data to be searched for the match. It must be compatible with the lookup_value. Depending on the search_mode, it may need to be sorted data.

• match_mode - This parameter indicates if the match needs to be an exact match or an approximate match. The default is to perform an exact match. Here are the values for the different options:

  • 0 - exact match. This is the default. It returns #NA or if_not_found message for no match

  • -1 - try exact match, but if none, then return the closest smaller item. (Similar to MATCH default)

  • 1 - try exact match, but if none, then return the next larger item. (No equivalent operation in MATCH)

  • 2 - do a wildcard match (*, ?, ~ ). "*" matches on multiple characters. "?" matches on a single character. "~" followed by a "*" or "?" matches on the literal asterisk or question mark.

• search_mode - Use this to make the search more efficient.

  • 1 - Search starting with row 1, i.e., the first item in the array. This is the default.

  • -1 - Search starting with the last row, i.e., the last item in the array.

  • 2 - Use a binary search. The lookup_array must be sorted in ascending order. Use for large table searches.

  • -2 - Use a binary search. The lookup_array must be sorted in descending order. Use for large table searches.

Examples of XMATCH

For the example of XMATCH, we will use the same range and table as was used in the textbook. The name of the person to find is "Jamie Johnson" and is given in F5. The lookup_array is in column A, rows 4 through 13. The result of the XMATCH is row number 5 as shown in F7.

Also in this example, we show how to use XMATCH as a nested funtion in the INDEX function. In this case, we want to find out what commission percentage Jamie is earning. So the lookup_array for the INDEX function is the entire table, A4:D13. The row number is calculated by the XMATCH function, and the column is #3 for the commission percentages. Cell F10 contains the formula.