In May 2019, Microsoft Excel Developer Team Lead Joe McDaid announced a new feature to replace the legendary VLOOKUP. The new function received a juicy English name XLOOKUP and a not very intelligible VIEWERX (moreover, the last letter here is the English “X” and not the “ha” – funny).
Six months Microsoft trained on cats tested this feature on its employees and insider volunteers, and finally, in January 2020, it was announced that XLOOKUP is ready for use and will be sent out with updates to all Office 365 subscribers shortly.
Let’s see what are its advantages over the classic VLOOKUP, and how it can help us in our daily work with data in Microsoft Excel.
Good old VLOOKUP
Suppose we are faced with the task of finding a price in the price list, for example, for buckwheat. Using the usual VLOOKUP function (VLOOKUP) it would be solved like this:
Just in case, let me remind you:
- The first argument here is the desired value (“buckwheat” from H4).
- The second is the search area, and be sure to start from the column where the required data is stored, i.e. from the product, not from the article.
- The third is the ordinal number of the column in the table from which we want to extract the value we need (the price in the fourth column).
- The last argument is responsible for the search mode: 0 – exact search, 1 – search for the nearest smallest value (for numbers). Moreover, 0 is not implied by default – you need to enter it explicitly.
Habitual, familiar and done by many on the machine, without regaining consciousness. OK.
Now let’s see how the same can be calculated using the new function VIEWX (XLOOKUP).
XLOOKUP Syntax
First, for the sake of order, let’s sound the official syntax. Our new function has 6 arguments:
=VIEWX(search_value; lookup_array; return_array; [if_nothing_found]; [match_mode]; [search_mode])
It looks a bit cumbersome, but the last three arguments [in square brackets] are optional (we’ll deal with them a little later). So, in fact, everything is simpler:
- The first argument (lookup_value) − what are we looking for (“buckwheat” from cell H4)
- The second argument (lookup_array) is a range of cells, where are we looking for (column Product in the price list).
- Third argument (return_array) − the range from which we want to get results (column Price in the price list).
When compared with VPR, it is worth noting that:
- The default is exact search, i.e. it is not necessary to explicitly prescribe this as in the VLOOKUP (last zero).
- No need to count and set the column number (third argument of VLOOKUP). In large tables, this can be tricky (especially with hidden columns).
- It follows automatically from the previous paragraph that inserting/deleting columns in the price list does not break the formula (as it would be with VLOOKUP).
- No, pproblems “left VPR”, when you need to extract the value to the left of the viewed column (for example, the article in our case) – the viewed and returned arrays in VIEWX can be located anywhere (even on different sheets, in the general case!)
- In general, the syntax is much simpler and clearer than that of VLOOKUP.
It’s also nice that VIEWX works great in the horizontal version without any modifications:
Previously, for this it was necessary to use the function GPR (HLOOKUP) instead VPR (VLOOKUP).
Error Catching #N/A
If the desired value is not in the list, then the function VIEWXAs VPR, gives the familiar error #N/A (#N/A):
Previously, to intercept such errors and replace them with something more meaningful, a nested construction of functions was used. IFERROR (IFERROR) и VPR (VLOOKUP). Now you can do everything on the fly using the 4th argument [if_nothing_found] our new feature :
Conveniently.
Approximate search
If we are looking for numbers, then it is possible to search not only for an exact match, but also for the closest smallest or largest to a given number. For example, to search for the nearest discount corresponding to a certain quantity of goods or a tariff to calculate the cost of delivery for a certain distance.
In the old VLOOKUP, the last argument [interval_lookup] was responsible for this – if you set it to 1, then the VLOOKUP switched to the search mode for the nearest smallest value. In VIEWER, the 5th argument is responsible for this functionality [match_mode]:
It can work in four different scenarios:
- 0 – exact search (this is the default mode)
- -1 – search for the previous one, i.e. nearest lowest value (for 29 items, it will be 5% discount)
- 1 – search for the next, i.e. the nearest largest (for 29 pieces of goods, this will already be a 10% discount)
- 2 – inaccurate text search using wildcards
If everything is more or less clear with the first three options, then the last one should be commented additionally. This refers to the situation when we are looking for a value where, in addition to letters and numbers, wildcard characters are used * (asterisk = any number of any characters) and ? (question mark = any single character).
In practice, this can be used, for example, like this:
Note that, for example, cabbage is written differently in the price list and the order form, but VIEWX still finds it, because we are no longer looking for just cabbage, but cabbage with asterisks glued at the beginning and end, and the fourth argument of our function is 2.
The VLOOKUP function, by the way, has always been able to do this out of the box, so VIEWRX has no particular advantage here. But another nuance is important: the VLOOKUP function, with the approximate search enabled (last argument = 1), strictly required sorting the required table in ascending order. The new function perfectly searches for the nearest largest or smallest in an unsorted list as well.
Search direction
If the table has not one, but several matches with the desired value, then the VLOOKUP function always returns the first one, because searches exclusively from top to bottom. VIEWRX can also search in the opposite direction (bottom-up) – the last 6th argument is responsible for this [search_mode]:
Thanks to him, the search for the first and (most importantly!) Last match is no longer difficult – the only difference will be in the value of this argument:
Previously, to find the last match, you had to do a lot of magic with array formulas and several nested functions like INDEX, LARGE, etc.
Summary
If you have read this far, then I think you have already drawn your own conclusions 🙂 In my opinion, Microsoft managed to create a very worthy successor to the legendary VLOOKUP function, adding power and beauty while maintaining simplicity and clarity of use.
The only downside so far is that this feature will only be available to Office 365 subscribers in the near future. Users of standalone versions of Excel 2013, 2016, 2019 will not receive this feature until they upgrade to the next version of Office (when it comes out). But, sooner or later, this wonderful feature will appear for most users – then we’ll live! 🙂
- How to use the VLOOKUP function to look up and lookup values
- Left VLOOKUP
- Linking the INDEX and MATCH functions as an analogue of VLOOKUP