Latest

Excel: What is X.LOOKUP (XLOOKUP)?

Recently, Microsoft introduced X.SEARCH, the successor to the well-known VLOOKUP (or XLOOKUP and VLOOKUP respectively in international versions of Excel). This feature was rolled out in Excel 365, 365 for Mac and the web version from April to July, but was overdue for Excel 2019. The classic version of the software uses the less flexible predecessors.

According to Microsoft, VLOOKUP is one of the top three Excel functions, preceded only by SUM and AVERAGE. Vertical search allows users to find a value and compare it to a range in a related column to the right to return a Boolean value for matches in the range. The feature was immediately introduced with the first Excel version in the 1980s. VLOOKUP introduced large groups of users to the advanced data selection possible with the spreadsheet software, further enhancing its value over paper.

Benefits of the new function

A limitation of VLOOKUP is that a search works from left to right only. The HLOOKUP function only searches data from top to bottom in a table. X.SEARCH replaces both functions and allows you to search from left to right and right to left. X.LOOKUP is much more granular in determining which data is compared and which is returned. By the way, VLOOKUP and HLOOKUP are still usable in Excel, but Microsoft recommends that users learn XLOOKUP because it has many advantages over the previous functions.

X.LOOKUP basically uses three arguments in parentheses: the value you want to look up, the data range you want to search in, and which data element you want to return. In the example below, this is the country (Brazil, F2), the search range of countries (B2: B11) and the return array (D2: D11), which returns the value from D6 as a match.

A big advantage, which changes the syntax compared to VLOOKUP, is that you specify separate arrays for lookup (B2: B11) and return (D2: D11). With VLOOKUP, the argument would be B2: D11, followed by the column index (the first index is always 0, not 1, so the fourth column has index 3) of the return value. SEARCH can return an array with multiple values ​​by this method, which is useful when you want to see information from multiple table columns with a search. In that case, the return range could be stretched to say D2: F11, if columns E and F exist.

Syntax X.LOOKUP

The difference in syntax is that the table array (data range) and column index are replaced for the lookup_array (search range) and return_array (return range). Also the approximation argument of VLOOKUP (FALSE or 0) is dropped. XLOOKUP searches for an exact match.

In the old function, a formula looks like this: VLOOKUP (lookupvalue, tablearray, column index, approximation). In the above practical example, that is = VLOOKUP (F2, B2: D11,3, FALSE).

In X.SEARCH that becomes X.LOOKUP (lookupvalue, lookup_array, return_array). In the above practical example, that is = X.LOOKUP (F2, B2: B11, D2: D11).

XLOOKUP resolves the following limitations of VLOOKUP:

  • By default, a VLOOKUP formula approximates a value and does not return an exact match. Users must add a fourth argument (with FALSE) to get an exact match. It’s very easy to overlook this and not realize that VLOOKUP doesn’t return exact matches.
  • VLOOKUP takes the index of a column as an argument. When adding columns to the left of the indexed column, this argument must be incremented by the number of columns added. X.LOOKUP supports adding or removing columns.
  • VLOOKUP cannot look to the left. The search always starts from the first column and focuses on results in a column to the right of it. It is not possible to look up values ​​to the left, so Excel users have to adapt their data to the formula, instead of the other way around.
  • The same goes for searching at the end of the data: if you want to see the last entry, you have to reverse the order of the data.
  • It is not possible to find the next greater value. With an approximate match, only the next smaller values ​​can be displayed and then only if they are sorted correctly.
  • More cells are included in the range than necessary. The table_array covers everything from the search column to the results column. This means that more cells are included than necessary, which leads to unnecessary data processing and reduced workbook performance.

Related Articles

Back to top button