A little from column A, a little from column B: XLOOKUP is the new, easier way to retrieve information in Excel spreadsheets.
Should your company choose Microsoft Office 365 as a productivity suite?
G Suite from Google is not the only cloud-based office software package. Microsoft’s Office 365 brings Word, Excel, Outlook and more to the cloud.
If you work on your accounts and you have to convert all your foreign expenses to the currency in which you file your tax return, you must know the exchange rate for the date of each expense. You don’t have to enter it manually: you can get a list of daily exchange rates and get Excel to look up the correct conversion for each issue. You can also use VLOOKUP when you need to find a part by part number, an employee by employee number, the international access code for a country or something else that you look up with a key from a master record that does not make sense to copy to your current spreadsheet.
But if you only do that once a year, you’ll probably have to investigate how you can use the VLOOKUP function in Excel.
For the third most used function in Excel (after SUM and AVERAGE), VLOOKUP is complex. By default, approximate matches are found, although that is almost never what you want, so you must remember to set FALSE in the function. And it is not flexible: if you add or remove a column in the sheet where you look up digits, the formula is not updated as for SUM, so you have to change your VLOOKUP formula manually. You must arrange the source data so that the index column, such as the date, is on the left and the desired values, such as the speed, are on the right; you cannot find the day when the exchange was the worst or the best without having a second copy of the data with the data in a different order. You cannot change the search order, and if you want to look up information in rows instead of columns, you must use another function, HLOOKUP, instead.
VLOOKUP can also be a performance hit because it creates an array that covers the index column, the column with the data you need and all other columns in between – even if you don’t care about the data in between.
Image: Mary Branscombe / TechRepublic
The new XLOOKUP function solves all these problems because they are simpler, more flexible and do not slow down your spreadsheets in the same way. You can look up results from rows or columns, and the data column does not have to be to the right of the index column. You can point XLOOKUP at multiple columns and retrieve more than one piece of information – for example, you can look up both the name of the employee and the department where they are located. You can specify a custom error to fill in (such as ‘name not found’) if a match is not found instead of getting the default # N / A. You can adjust the search order as well as how matching works. And the result that you get back is a reference, not a value, which means that you can pass it on to another formula.
XLOOKUP needs at least three parameters: the term you use to search, where you must search for the term and the data you need to retrieve and where you need to place the data that you are looking up.
The first term, lookup value, can be a cell with the value you are looking up, a value that you type in the formula, such as a name, or another formula such as a calculation. That is the same as with VLOOKUP, but it can also be a concatenation of multiple cells in an array – for example, B1 & B2 & B3 looks for the value where all three cells match – instead of just a single cell to match up .
LEARN MORE: Office 365 Consumer prices and functions
But instead of an array such as A2: D400 telling VLOOKUP to search cells A2 to A400 for the lookup term and then retrieving the value from the same row in column D, XLOOKUP uses two parameters – lookup_array and return_array. For the same lookup that would be A2: A400 and D2: D400. Instead of creating an array with almost 1600 cells, XLOOKUP only needs to process a little less than 800. The larger the spreadsheet from which you look up, the greater the performance difference that makes.
And if you insert a new column between the index column and the column that contains the results, the formula is automatically updated instead of breaking the way it would with VLOOKUP – because you get results from the wrong column.
To view multiple columns for the corresponding value, merge them with & – C2: C200 & E2: E200.
You can look up data in any direction with XLOOKUP.
Image: Mary Branscombe / TechRepublic
If you want to know which location has the highest turnover, who uses the majority of their disk quota or who has used the least vacation days, use a MAX or MIN formula in the lookup_value. This is more flexible than with VLOOKUP because you do not have to rearrange the data, so that the value with which you look up information is always in the leftmost column; XLOOKUP can look left or right, up or down – you simply specify in which columns or rows you want to look.
If you look at those figures in the same spreadsheet, you can simply use the total row of a table and set the total to MAX or MIN or use conditional formatting to retrieve information. But if you want to get that minimum or maximum to use in another spreadsheet, you need a lookup function.
If you want to look up multiple pieces of information with the same search, you can still use an array, but you can retrieve everything with a single search instead of needing one for each piece of information you want to get back. So to search for the employee’s name in column C and their department in column D, enter the return_array as C2: D400.
If you are satisfied with the standard search order and error, that is all you need to do your search. It is an easier formula to understand than VLOOKUP. But you can also adjust the lookup.
Combine the match_mode parameter and the if_not_found parameter to find out which group a number falls into and explain which results do not match any group.
Image: Mary Branscombe / TechRepublic
If you want a custom error, such as ‘name not found’ or ‘no exchange rate for this date’ or something that states more clearly than # N / A that there was no match to return, put that between the quotes parameter (which is called (if_not_found)).
You usually look for the exact answer – for example, a name or the exchange rate for a specific day. But you can also look up where on a scale a number falls, such as finding in which tax bracket a salary falls. In that case, you may not be able to get an exact match. Set the fifth parameter (match_mode) to 1 and if there is no exact match, you get the next largest result; -1 retrieves the next smallest item. If you match a salary that falls within a tax limit, you want to match the next largest result because tax limits apply up to a maximum figure. So a salary of £ 30,000 must look up the 20% tax rate that applies to £ 50,000: matching for the next largest result will get that.
SEE: How to become a developer: A cheat sheet (TechRepublic)
Set the match_mode to 2 and you can use the usual Excel? and * wildcards to specify what to match. That way you can search for South and Southeast with ‘South *’ or Northwest and Southwest with ‘* west’.
The default search for XLOOKUP is top to bottom: set the sixth parameter (search mode) to -1 if you want to search at the bottom of the list until you find the first match.
Function to function
You can nest XLOOKUP functions to extract a cell from a table by looking up the labels in the top row and left column: use the first XLOOKUP to find the first label and enter another XLOOKUP function as the return_array to find the second label and the result is the contents of the cell where they intersect. (That’s the same as using the INDEX and MATCH functions, but you don’t have to learn two functions yet.) The easiest way to do this is to have the same labels above and next to the cell where you get the result want to see and use those labels to match.
The result that comes back from XLOOKUP is a reference to a cell instead of a copy of the value in it, so you can pass that on to another formula. If you do two XLOOKUPs, you can change that into an array and use SUM to add all the cells in between, or MAX to find the largest values between the two. This way you can look up the exchange rate at the beginning and end of the month and show the highest rate.
XLOOKUP is currently in beta; you can only get it if you are enrolled in the Office Insiders program. Microsoft has been flying to all Insiders with XLOOKUP since the beginning of September, but it is possible that it has not reached everyone yet, so keep checking if you do not see it. If you are already using it, please note that it may change before it is sent as the last function. Since XLOOKUP was first introduced, the parameter (not_found) was added (this was originally the sixth parameter, but then moved to the fourth parameter so you didn’t have to enter empty parameters to use it).
When it is sent, it is available as part of an Office 365 subscription and then in the next permanent version of Excel that has been released. So if you have already purchased a permanent license for Excel 2019, you will not receive it until you upgrade, just like with any other subscription function. If you always want the latest Office features as quickly as possible, that’s what the Office 365 subscriptions are for.
Weekly newsletter from Microsoft
Be Microsoft’s insider for your business with the help of these Windows and Office tutorials and the analyzes of our experts on Microsoft business products.
Delivered on Monday and Wednesday
Register today