If you’re building large models, then you may want to use volatile functions – including OFFSET(), INDIRECT(), and TODAY() – with caution, because unless you know what you are doing, they *might* slow Excel down to the point that data entry is sluggish, if not downright tedious.
In fact, you *might* want to consider getting out of the habit of using these functions at all if there are alternatives, and you might want to replace volatile functions in your existing models with non-volatile alternatives…I have reduced recalculation time in large models from minutes to milliseconds by doing just that!
So what the heck does volatile actually mean? And why should you care? Let’s find out, shall we?
How does Excel update all those cells?
Let’s take a look at how Excel ensures that each cell has the right number in it when you make a change somewhere. But first, a disclaimer: Note that this is an introductory article, and so is necessarily simplistic. If you want to know more about the specifics of this complicated subject, check out the links to Excel MVP Charles Williams’ excellent site at the bottom of this article. Okay, disclaimer ends…
A large Excel model might have several hundred thousand cells with formulas in it. Maybe even several million. Most of these formulas will reference other cells, and many of those cells will have formulas in them that reference other cells in turn, and so on. If a formula in a Cell A2 refers directly to Cell A1, then A2 said to be directly dependent on A1. Obviously if A1 changes, we need those changes to flow through to A2. And when recalculating the entire workbook, we need A2 to be recalculated AFTER A1 has been recalculated. That’s called a dependency chain.
Large models can have a number of very long dependency chains comprising of hundreds of thousands of cells that run across worksheets or even between workbooks. To keep track of how all these cells interrelate – and to ensure that a change in any specific cell’s value correctly flows through to any other cells that may depend on it – Excel builds and maintains what is known as a ‘dependency tree’. Think of this as a big flow-chart or circuit diagram showing how all the cells in one of these giant formulas interconnect. Excel maintains this dependency tree every time you make a change to a formula in a cell, by looking at the argument list of each separate function within that formula. And this dependency tree is saved along with the file itself.
Smart Recalculation
Thanks to this dependency tree, when you change the value in one cell, Excel can work out what other cells might be affected. And so Excel can smartly recalculate just those particular cells. Meaning it doesn’t have to blindly recalculate the whole workbook just because one fairly insignificant part of it might have changed.
So let’s say you change the value of a cell somewhere that has only one other cell pointing at it (and no further cells depend on that other cell). Thanks to smart recalculation, Excel only recalculates the value of the cell you just changed, and the value of that ONE dependent cell. It doesn’t have to recalculate the entire workbook.
Likewise, if you change the value of a cell somewhere that has many, many cells downstream, then Excel of course has to recalculate all of the cells further down that particular chain. But it can safely ignore any cells further up that particular dependency chain. And it can ignore any cells elsewhere that aren’t in this particular dependency chain.
If a long-enough part of a dependency chain gets recalculated, then you might well see the word ‘calculating’ in the status bar while Excel works its way through all the relevant cells in that chain. But usually, this recalculation happens so fast that the word ‘calculating’ flicks on and off so quickly that you don’t notice it.
Not-so-smart recalculation thanks to volatility
Now here’s the important bit: a particular class of formulas called volatile formulas get automatically recalculated any time you enter data anywhere in any open workbook – even if the thing you just changed had nothing to do with those volatile functions. And then this triggers Excel to then recalculate all directly dependent cells downstream from those volatile formulas too. Yikes!
This mean that if you’ve opened a very large spreadsheet model with volatile functions in it – and if those volatile functions have a large number of formulas downstream (or a smaller amount of resource intensive formulas) – then if you are say trying to add items to a shopping list that you’ve started in another workbook it could take minutes for you to add each item to that shopping list, because every time you add an item, it triggers an avalanche of unnecessary and pointless recalculation in the large spreadsheet model.
The fact that each and every cell ‘downstream’ of any volatile formulas get recalculated is an important point to get your head around. Many people think that slow calculation times due to volatility is due to the time it takes to recalculate large amounts of volatile functions in a model. But often most of that delay is in fact due to the recalculation of all the cells ‘downstream’ from those volatile functions. In other words, even just one volatile formula with a very long calculation chain hanging off it could cause you grief. And if that calculation chain gets more and more complex, so does the effect of that one volatile formula.
Here’s how that looks visually:
In fact, it’s not just entering data that will trigger a volatile function to recalculate, but also these things (among others):
- Deleting or inserting a row or column.
- Performing certain Autofilter actions.
- Double-clicking a row or column divider (in Automatic calculation mode).
- Adding, editing, or deleting a defined name.
- Renaming a worksheet.
- Changing the position of a worksheet in relation to other worksheets.
- Hiding or unhiding rows (but not columns)
So almost anything can set off that domino effect. Which reminds me of this:
(And what the heck…if you enjoyed that, then click this link too. But hurry back…this post is getting cold).
So which functions are Volatile?
These ones:
- NOW()
- TODAY()
- RAND() and RANDBETWEEN()
- OFFSET()
- INDIRECT()
- INFO() (depending on its arguments)
- CELL() (depending on its arguments)
If you’re an intermediate Excel user, then chances are that you already use some of these regularly. For instance:
- OFFSET() is usually the function of choice to anyone who wants to create dynamic ranges
- Many large models make use of the INDIRECT() function to construct cell or range references “on the fly” in response to some choice that a user makes
- Many large models make use of the TODAY() function to check if a date entered by a user occurs in the past, present, or future.
When does this matter?
Most of the spreadsheets you use these functions in are so small that you probably don’t even notice any extra volatility-related recalculation. So no harm done. However, if you’ve ever had that a large spreadsheet that seems particularly sluggish when you’re trying to enter new data – or that seems to impact the performance of other open workbooks – then chances are you know exactly what I mean.
I’ve seen frustrated-looking users waiting for as long as one to two minutes for particularly large models to recalculate after each and every change they make to it, even if those changes are relatively insignificant, such as changing the spelling of a column header.
Often spreadsheets like this get so sluggish that users switch Excel’s calculation setting to Manual, just so they can make changes in a timely fashion, and then switch it on again when they’re done in order to have the model calculate the correct answer. This is dangerous…I’d never set calculation to manual if I could help it. There’s just too much chance that someone someday will use output of such a model without remembering to set calculation to Auto. What’s worse, when you open two workbooks, one saved in manual mode and one saved in automatic mode, they will both have the calculation mode of the first workbook opened. I have seen many cases in my career where analysts have done just that…opened a workbook with calc set to manual, opened a whole bunch of others where calc was set to auto, and then done an entire day’s work without realizing that calculation was subsequently turned off for all of them. Doh!
Here’s a slide from my Excel Efficiency presentation that warns users not to do this:
Previously you might have thought that you had no choice but to switch calculation to Manual, because you might have thought that this sluggishness is an unavoidable consequence of the size and complexity of your spreadsheet. But now you know that it *might* be caused by use of volatile functions, and that volatile functions might not be suitable for some occasions…particularly if you’re building large models that utilize these functions at key points within your model. Replace those Volatile functions with some non-Volatile alternatives, and you’ll likely find that your model stops being a slow dog, and starts being a much faster greyhound. To the point that you can switch calculation back to Automatic again.
What are the alternatives to Volatile functions?
While volatile functions like OFFSET() and INDIRECT() are incredibly useful, you can usually achieve the same thing by using other non-volatile formulas such as INDEX or CHOOSE, as well as through leveraging off the dynamic references that Excel Tables allow.
And instead of the TODAY() function, you can use VBA to populate today’s date as a hard-coded value in big models, as you’ll see in the download file below. Check out the Alternative Functions tab of that file to see some examples of common use of volatile functions, as well as some non-volatile alternatives.
If you’re struggling to find a non-volatile replacement for an existing volatile formula, then you can always post a question on the Chandoo Forum asking for some advice on non-volatile alternatives.
Am I being over-zealous here?
As we’ve seen, too much reliance on volatile functions *might* trigger large parts of a model to be recalculated needlessly. But it’s worth remembering that this is only going to be noticeable in particularly big spreadsheets. So perhaps I’m being a little overzealous here. So if you know what you’re doing, then maybe you don’t want to dismiss volatile functions outright. After all, you can always assess your options on a case by case basis: try them out, test, test, test, test again, and then make a balanced decision.
However, if you know of an alternative formula combination that does exactly the same thing as a volatile formula, then I’d suggest that you get into the habit of using that instead whenever you can. That way you won’t inadvertently have issues when it really matters. And I’d suggest that if you don’t have much experience of functions and performance, then perhaps it’s safest to simply err on the side of caution and steer clear of volatile functions altogether.
So not only do I see little down side to avoiding volatile formulas, but I see a significant upside: I’ve seen plenty of large models built by the likes of the big 4 accounting/consulting firms that make heavy use of volatile functions, and that consequently have recalculation times so long that they are effectively unusable. Stripping out the volatile formulas from these models has resulted in delays from data entry falling from upwards of two minutes to well under a second. Not to mention that users can now work on other files while these models are open, without fear of triggering an avalanche of unnecessary and pointless recalculation. Had these model builders known to avoid volatile functions, they would have saved users a lot of grief.
Excel MVP and Recalculation Expert Charles Williams says:
The better use you make of smart recalculation in Excel, the less processing has to be done every time that Excel recalculates, so avoid volatile functions like INDIRECT and OFFSET where you can, unless they are significantly more efficient than the alternatives. (Well-designed use of OFFSET is often fast.)
In fact, on Charles’ website he goes so far as to say avoid volatile functions wherever possible.
With all that in mind, I’ve made a personal choice to steer clear of volatile functions where I can. Your mileage may differ. Regardless, the subject of volatility is definitely something that intermediate users should be made aware of. What they do with that awareness is up to them. But forewarned is forearmed.
Fancy a demonstration?
Sometimes it’s most helpful to see something with your own eyes. So download this file, open it, and enable macros: Volatility-demo-using-TODAY-20140230
You’ll see it has a dropdown in it, where you can choose to either populate a cell with the volatile TODAY function or with a hard-coded date:
Downstream of that drop-down output cell are 20,000 formulas spread across two columns:
If you choose the Use Volatile TODAY() Function option from the dropdown, then whenever you enter data in that 3rd ‘Completely independent cells’ column then you should notice a significant delay. Change that dropdown to ‘Use Hard-Coded Date’ and you should experience significantly less delay, if any.
You’ll also see a blue button you can click, that will time how long the delay is under each option:
On my system, there’s about a 1 second delay when using the TODAY() option, and almost no delay when using the hard-coded date. (Note that you have to click the blue button twice after you change that dropdown to get the ‘proper’ reading. The first reading will be artificially high.)
Why are some functions volatile?
The reason for some of these functions being volatile this is fairly obvious. For example:
- NOW() should always return the time as at the last calculation, so needs to be refreshed any time new data is put into the workbook, in case one of your formulas does something specific based on the time of day.
- TODAY() similarly must be refreshed to ensure than the day hasn’t changed since the last time something was entered into the workbook (which will be the case, if someone works past midnight, or if they come in in the morning and make a change to a file that they had left open the previous night.)
But the reasons for others being volatile – such as OFFSET and INDIRECT, which are often used by modellers to create dynamic named ranges – are less clear. First, let’s look at what OFFSET and INDIRECT actually do:
- Offset Returns a reference to a cell or a multi-cell range that is a given number of rows and columns from a given reference. So OFFSET($A$1,1,2,5,3) says “Go one cell down from $A$1 (which takes us to $A$2), then two cells across (which takes us to $C$2) and then return a block of cells 5 down from $C$2 and 3 across from $C$2 (which gives us the range $C$2:$D$6)
- Indirect Returns the reference specified by a text string. References are immediately evaluated to display their contents. So Indirect(“$A1”) tells Excel “Go look in cell $A$1, and tell me what’s in it”.
So why would that mean they need to be volatile? Because Excel constructs dependency trees based on cell references.
- INDIRECT() has an argument that is constructed out of text – e.g. INDIRECT( “$A1”). This might look like a cell reference, but it is not. In fact, the argument of an INDIRECT function might equally look something like this: INDIRECT(“$B”&$C$9-2).
- OFFSET() takes numerical arguments, which point to a cell reference, but are still just numbers.
- In order for these to form part of Excel’s dependency tree, the Excel dependency tree algorithm would have to first evaluate text like INDIRECT( “$A1”) or the numerical arguments like OFFSET($A$1,1,2,5,3) in order to determine what the associated cell reference actually is, before adding it to the dependency tree. Maybe the Excel obviously made the call that rather than introduce this extra step where these two functions are concerned, they may as well just make both functions fully volatile.
But given that you can set up INDEX() do much the same thing as OFFSET(), why doesn’t INDEX need to be volatile too? I imagine it’s because INDEX uses range arguments, whereas OFFSET uses numerical arguments. So Excel can extract these range arguments directly from an INDEX() function when building/amending the calculation dependency tree.
Note that INDEX() is what’s called semi-volatile, meaning it gets recalculated when the workbook opens.
And also note that any formulas used in conditional formatting effectively become what Charles Williams calls super-volatile: they are evaluated each time the cell that contains them is repainted on the screen (which happens say if you use the scroll bar to move the ‘view’ up/down or left/right), even in Manual calculation mode. But because no other formulas are ‘downstream’ from conditional formats, then only the conditional format formulas themselves get recalculated. So if you’ve got simple conditional formatting rules, you won’t notice any delay.
More info:
I’ll talk about alternatives to using volatile functions in a series of upcoming posts. But meanwhile…if you’re not feeling too sluggish…then check out these great links from Excel MVP Charles Williams.
- Excel 2010 Performance: Improving Calculation Performance
- Smart Recalculation
- Volatile Excel Functions
- Excel Dependencies
- Evaluation Circumstances
- Writing efficient VBA UDFs Part 10 – Volatile Functions and Function Arguments
Pretty much everything I’ve covered in this post came from Charles’ writings, so I’d like to acknowledge the work he has done in explaining this complex subject to countless Excel users over the years. Charles also sells a great add-in called FastExcel for profiling Excel calculation performance and memory useage – so be sure to check that out if you want to get serious about diagnosing volatility issues with your own Excel models.
You may also be interested in Jan Karel Pieterse’s RefTreeAnalyser utility, which among other things allows for easy Auditing of formula dependents and precedents, helps you trace errors, and will let you time your workbook calculation for each worksheet to find bottlenecks as well as check columns for formula inconsistencies. Jan Karel has a free demo version with limited functionality, if you’d like to take it for a spin.
Let me know your thoughts in the comments
This has been a particularly taxing post to write. So if you found this article helpful, please let me know below in the comments. If you’re not following something I said, or can think of a better way to say it, then let me know that too.
About the Author.
Jeff Weir – a local of Galactic North up there in Windy Wellington, New Zealand – is more volatile than INDIRECT and more random than RAND. In fact, his state of mind can be pretty much summed up by this:
=NOT(EVEN(PROPER(OR(RIGHT(TODAY())))))
That’s right, pure #VALUE!
Find out more at http://www.heavydutydecisions.co.nz
67 Responses
Sure it’s a nice new command. It would be useful if everyone had access to it. But if there is any chance you will be sharing the file with someone who has a onetime payment Office license, or an older version of Office you can’t use it.
That is my biggest gripe with many new features MS is launching. With such vast userbase and existing spreadsheet “systems”, all of these formulas are going to create more trouble than imagined. That said, we should learn new things, especially if you move to a new job chances are you will be using a different version of Excel there.
I love to learn new things, like this new command. But I can’t afford, literally don’t have the money, to keep paying for 365.
This is the thing that especially offends me about the Office 365 pricing scam/scheme. Sure, if they want to milk more money from users using the rental scam, fine I know I don’t have to fall for it. But restricting new “features”, like new commands to 365 is offensive. It makes one-time payment users “second class” customers, especially anyone who has paid for Office 2019. At least in the past new features/commands came only came out every few years, with new versions so there was some logic to the separation. But now the new features are coming every few months and there is no real separation between 2019 and 365, but still they limit the new features to 365. Even 2016 is close enough. MS “accidentally” pushes a few new features to 2016, when they feel like it or when they are too lazy to do the extra work to prevent them from going to 2016.
I agree with Ron I have MS Office 2019 which I used for Charity work but a pensioner I find the cost of the MS365 unaffordable. Perhaps there is some way for a Ms Guru to perhaps create 3rd party update for the stand alone versions.
I will however continues with Ms 365 this year as I have just renewed the subscription
thanks very much for keeping us abreast of latest developments and also the excel community for their useful feed back
regards Brian 18/03/2024
Good point. I suggest using the free MS Office online (you just need onedrive account) to maintain old files and work on them. The only limitation is that it is browser based, so you won’t be able to do many advanced things. But it is better than the alternative of shelling out $100+ every year.
Yes, of course this is the latest and excellent update from Microsoft but this feature will take years to come in the market because most of the people or offices are still using Office 2007 or 2013.
Dear Chandoo Sir
Thank you for updating latest idea this idea is centralized lookup formula all about.
this idea is realy impressive and samart
I couldn’t observe any benefit, over MATCH+INDEX.
Hmm, the base scenario is similar to index+match, but XLOOKUP makes life simple with single formula and default “exact match” setup. Plus I find the “lookup from last” and “less than” “greater than” options very useful and less cryptic than MATCH options.
Thanks for sharing, it added some excitement to my Friday morning! I don’t have 365 but am still excited to be aware of the existence of these features! I know that vlookup on larger sets of data can really take up some resources–it makes sense, it’s performing a lot of operations for us while we sit and sip on coffee. 😉 However, I’m wondering if you’ve you noticed a difference in performance with xlookup? Is it slower, faster, or pretty much the same in terms of calculation speed?
I haven’t tested it against VLOOKUP or INDEX+MATCH. If anything, I would guess that the performance should be similar as they could all use same logic internally. I will try this and share some outcomes later.
I would love to know the results. We’re crunching a ton of data and I love the simplicity of XLOOKUP, but we can’t handle the sluggishness of VLOOKUP. I hope XL is faster!!!
I believe XLOOKUP has been written to deliver exact matches at the same speed as a binary (vlookup’s approximate) search.
Here is a nice overview of differences in performance of different lookup formulas. Unexpected, but XLOOKUP is not always fastest.
https://professor-excel.com/performance-of-xlookup-how-fast-is-the-new-xlookup-vs-vlookup/?amp#What_is_the_8220binary_search_mode8221_of_XLOOKUP
You can use an if logic to wrap around a vlookup with a TRUE argument to speed up lookups.
A nice addition to the function list. Very usefull and easier to use then INDEX + MATCH.
Since XLOOKUP is in beta testing, it would be great if Microsoft development team added a 5th. argument: if_na. That is: if XLOOKUP returns #N/A, an alternate value could be returned instead. Therefore, it wouldn’t be necessary to do =IFNA(XLOOKUP(…), value_if_na).
Good idea. But I feel this can be a dangerous precedent as no other formula in Excel has fail-safe option (other than IFERROR and IFNA ofcourse). So may be leave it to return error.
Don’t overlook the new FILTER function. That has a final [if_empty] setting.
Although I don’t have and expecting to be around soon in EXCEL 2019, my question is there a way to work around the new function “xlookup” but not the old ones.
However it is appreciated tip,thanks
Chandoo
You can also use XLookup like
=Sum(xlookup():Xlookup())
Refer the example 4 at:
https://support.office.com/en-us/article/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929?ui=en-US&rs=en-US&ad=US
This makes it hugely powerful as it is returning an address like Index can do
Great point Hui. I am yet to find a practical use case for summing between lookups, but I am pretty sure others will find this useful.
Here is an idea.
If you wish to analyse data for a given month, the relevant portion of the Sales table (sorted by date) is given by
= XLOOKUP( EOMONTH(month,0), EOMONTH(+sales[Date],0), sales,0,1 ) :
XLOOKUP( EOMONTH(month,0), EOMONTH(+sales[Date],0), sales,0,-1 )
which can be referred to as a named formula ‘selected’. Being a reference to the original table, range intersection with columns works. Hence
= XLOOKUP( MAX(selected sales[Net Sales]),
selected sales[Net Sales], selected sales[Sales Person] )
provides an answer to
Who had most sales for February?
Caution: The formula requires 7 separate searches of the data but they are very fast.
I use VLOOKUP a lot with named ranges, are you able to reference those in XLOOKUP?
@Hamish… you should be able to use any reference styles that work with other formulas in XLOOKUP. So yes for names, structural, cell and references to other sheets / workbooks.
Hamish, Yes it all works perfectly. That includes cases in which the data table does not comprise raw data but rather is made up of dynamic arrays. Naming the anchor cell of each dynamic array allows expressions such as
= XLOOKUP( MAX(selectedNetSales#), selectedNetSales#, selectedSalesPerson# )
Conversely, if the returned field is comprised of anchor cells for separate dynamic lists (e.g. employment data for the specified salesman) then the list can be returned by adding ‘#’
=XLOOKUP(0,sales[Net Sales],EmployeeInfo,1)#
Since the documentation says it returns a reference array, could you write formulas that could answer questions that need to perform a function upon a result set that contains multiple rows such as:
1. What is the total Profit/Loss for SalesPersons named [Jamie]?
2. What is the MAX/MIN Net Sales for SalesPersons named [Jamie]?
3. What was the Average Net Sales for everyone that had exactly [8] Customers?
I think the answer to your question is ‘no’ unless you are willing to sort the table so that the records you wish to aggregate form a continuous range. That is, the formula
= SUM(
XLOOKUP(salesPerson,sales[Sales Person],sales[Profit / Loss],,,1):
XLOOKUP(salesPerson,sales[Sales Person],sales[Profit / Loss],,,-1))
only works if the data is sorted by Sales Person.
Otherwise it looks like SUMIFS (and similar) offers the best solutions with FILTER a close second.
= SUMIFS( sales[Profit / Loss], sales[Sales Person], salesPerson )
= SUM( FILTER(sales[Profit / Loss], sales[Sales Person]=salesPerson ) )
XLOOKUP allows us to look for a variable in a column and return a value from a row: combining VLOOKUP ad HLOOKUP in essence.
I watched a video last night in which the presenter showed an example that returned an error. The solution that the presented was using is this: =XLOOKUP(A4,B7:B9,C6:E6)
To see the problem in action, put a b c in the range B7:B9 and 1 2 3 in the range C6:E6 and in A4 enter a or b or c
I solved this problem in this way:
=XLOOKUP(A12,B15:B17,TRANSPOSE(C14:E14))
I have also set up a financial analysis example in which I wanted to find, for every line item in an income statement, which month was exactly equal to the mean of that row or which was immediately below the mean or immediately above it. Or Median, or Standard Deviation …
I used XLOOKUP() and IFS() together with Data Validation (although that is optional) and while the formula is a little unwieldy, again I am effectively combining vertical and horizontal lookups.
Excellent find and tip Duncan 🙂
Hi,
Can you please tell me if there is any way to return multiple values with a single match.
Thanks in Advance
when will be in excel 2019
Thanks
Never.
“New features” like the XLookUp() command are only added to Office 365. They will never be added to Office 2019. They may show up in Office V-Next, when ever it comes out, in the near future. MS has not yet announced a new version. If they follow the pattern in the last few versions that would be fall 2021. But that is only a guess.
I have it now in office 2021
I downloaded your sample spreadsheet and three of your first seven examples are incorrect. Then I stopped.
Which version of Excel are you running? XLOOKUP doesn’t work in any version except Office 365.
Hi, Chandoo.
Great tips, thanks!
In example #11, “What is the ‘net sales’ for Johnson? = 1540” the formula only takes into account the first match for Johnson (D10)?
In row 21 Johnson appears again so the correct answer should be 4192 (D10 + D21).
Imagine a DB with hundreds of records!
How can we deal with duplicates using XLOOKUP?
Thanks.
Is there an easy way to handle if the cell is blank in the data table to prove the result of a blank? With VLOOKUP, previously to get this result, I had to do:
=IF(VLOOKUP($B2,data,6,FALSE)=””,””,VLOOKUP($B2,data,6,FALSE))
I am hoping that I don’t have to resort to the same lengthy format. I did try the “Value Not Found” example you provided (love it). However that is when the search value is not listed, not when the search value is found and the result value is a blank cell.
Thanks for everything you do!!!!
Hi Sherry,
Are you using the IF formula to show “” instead of 0 ?
If so, you can use this structure
=XLOOKUP($B$2, data[col1], data[col6]) & “”
This will force 0 to convert to empty space. It won’t impact other results though, (assuming column 6 is text)
column 6 is a date.
A bit longer, but to force the ‘value not found’ you could remove the entry from the lookup array
= XLOOKUP(lookupValue,
IF(data[col6]””, data[col1]),
data[col6], “Missing data”)
Hi Chandoo,
I’ve been waiting for this function for months so that I could replace all my INDEX / MATCH / MATCH statements. However, I have hit a snag with using nested XLOOKUPs as replacements. If the inner XLOOKUP can’t find a value, then whatever value I specify as the [if not found] value causes the outer XLOOKUP to fail and return #VALUE. So the [if not found] functionality works if a single XLOOKUP can’t find the search value, but it causes nested XLOOKUPs to fail. Can you see any way around that?
Thanks
Hey Stuart… Can you share an example of what result you are expecting in nested case? One option is to use a single IFERROR outside all the nested functions.
@Stuart
Do not limit yourself to thinking of [if_not_found] as being a text string, e.g. “Oops”; it can be a formula in its own right, returning a default row from the original table or even a lookup from an alternative table.
What it must return is an array in order to form a valid parameter for the outer XLOOKUP.
Hi Peter,
You’ve got it! As you suggest, by setting the inner XLOOKUP to return an array full of zeroes (or whatever) solves the problem. The outer XLOOKUP can of course just have 0, or whatever, stated its if_not_found value.
I am surprised that I haven’t come across this issue or solution anywhere else. There are lots of blogs / videos which mention using nested XLOOKUPs as a replacement for INDEX / MATCH / MATCH. I can’t say I’ve read or watched them all, but the ones I have don’t mention this issue. I suspect there are / will be a lot of people getting #N/As or, worse, #VALUES depending on what they specify as the inner function’s if_not_found.
Thanks for your help!
I am trying to lookup a date and name and return the number of hours from another worksheet? If I’m mixing text and dates, will this still work?
Great article. But,…two questions:
1) I do have Office 365. Yet, the XLookup is not recognized by Excel. Your sample file displays a #NAME? Why?
2) In your samplefile you have a leading ‘_xlfn.’ in front of the formula. Why is that?
Hi Michael…
Can you confirm what is your current version of Excel is? Also see if you can update to newer version. You can do both from File > Account.
Great Job..
My values that I want to join are not exact, i.e.
000025868 and 0000258 68 Total
Is there a way to join the data?
Interesting. Assuming the space is in the lookup column, try this:
=xlookup(“000025868″, substitute(lookup_col, ” “,””), result_col)
Getting a #N/A as the results.
Is there a way to convert “0000258 68 Total” to 000025868 (or visa versa) before I run the =XLOOKUP?
If you just want to remove the word “total” at the end, use SUBSTITUTE for that. If there can be other words, you are better off first running the data thru Power Query so you can clean it.
One thing that is possible is to take a numeric lookup value and convert it to text before searching a text lookup array. For example
= XLOOKUP(TEXT( value, “0000000\?00\*” ), array, return, , 2 )
will perform a search with wildcards that allow “Total” to be appended or any character to be inserted two digits before the end of the number.
That would pick up
“0000258 68 Total”
but you would need an alternative test to match the number 25868, itself.
Check the reference, while selecting data the xlookup function automatically starts from new line. Try changing it to the first row and it would work.
YOU ARE THE EXCEL KING!
Thank you
Hi Chandoo,
I have 2 sheets with 5 columns. data in columns A:C is similar except that changes are made in columns A and C. I want to lookup in column C in Sheet2 and update Sheet1 columns A:C.
for example
Sheet1
ColA ColB ColC
123 AB12 One
234 BC23
323 CB22 Six
Sheet2
ColA ColB ColC
123 AB12 One
234 BB22 Two
323 CB22 Six
I don’t think we can claim that XLOOKUP “replaces” INDEX+MATCH. Yes, it provides a suitably powerful alternative, and is absolutely a full replacement for VLOOKUP and HLOOKUP, but it can’t easily play some of the “math” games that are possible with INDEX+MATCH and sometimes even necessary when the data isn’t in a convenient layout.
What if you needed the row above or below the match or if the data was laid out in repeating sections where you first needed to know the location of the section header and then the location of a given item within each section? Both of those problems can be solved with plus/minus shifting of the number returned from the MATCH.
So I would argue that INDEX+XMATCH are the true replacement for INDEX+MATCH, thus taking full advantage of the X — defaulting to exact matches, virtual sorting, and so on — while preserving the ability to “shift” the match as needed.
I’m looking for a price in a multiple column price list. With Vlookup, I specified the entire table and for the column, looked at the user selected model/column. In Xlookup, how to specify the column number and the range up and down or can I just specify the column number only?
One advantage that VLOOKUP retains over XLOOKUP is the ability to supply a lookup column number dynamically, as a purely numerical result of a calculation. To replicate this functionality using XLOOKUP, you would need seperate logic to calculate the column reference (i.e. the column’s number, range name or range address) and pass it to the XLOOKUP formula. You could do this inside the XLOOKUP function by setting up the 3rd param of XLOOKUP to be based on your “user selected model/column”.
Using Xlookup with “match mode” = -1 and “if not found” = “ABC”
Now if the lookup value is not found in the lookup_array excel gives the the highest value from the return_array.
This is not what I expect from xlookup.
It should return “ABC”
Can you explain why?
Chandoo,
I am having trouble with XLookUp. How do I get it to return multiple values such as employees with salary greater than $45,000 or to sum all the sales in the East region? Are these more pivot table inquires?
Is XLOOKUP more useful for finding one record than multiple records?
Thank you,
Jennifer Jeffords
Hi Chandoo,
Is it possible to use XLOOKUP to return a status such as “Checked” and “NoCheck”(something similar to IF stmt)
Thank you.
I used the index and match to look up the hourly rate for a job classification as a part of a drop down. Now, I want to calculate the hourly rate multiplied by hours worked and the cell will not calculate. What might be the problem? The results cell of the look-up is formatted to be currency?
You show return array can be more than 1 column but what about Look up array? What if I want to find a value than can be in 1 of 3 columns and then return one value from another column.
You can use XLOOKUP for such things too.
For example, if you have three columns: home phone, cell phone and email address
and a column with customer name
and you want to lookup the name of the customer when you specify any value from one of those 3 columns,
you can use the below XLOOKUP.
=XLOOKUP(TRUE,BYROW(C3:E22=I2,LAMBDA(a, OR(a))), B3:B22, “No record found!”)
Here I2 contains the search criteria (either home phone, cell phone or email)
B3:B22 have names
C3:E22 have the home / cell / email values
Hi my name is Musawir Rasool i am from India in a state of jammu and Kashmir I love watching your videos and lot from your videos
Thanks
And one more can u teach me full power bi?
Hi Chandoo,
I was referring to your xlookup-examples file, and in that I saw your formula for Sl. 8 – Who has least sales? You wrote formula =XLOOKUP(0,sales[Net Sales],sales[Sales Person],,1) but I think a more better way would be to write =XLOOKUP(MIN(sales[Net Sales]),sales[Net Sales],sales[Sales Person],,1). This is because your formula would not reliable unless you’re specifically looking for a salesperson who has exactly 0 in sales, which is not the same as the least sales — unless 0 happens to be the lowest. Also, the 1 as the last argument means “approximate match in ascending order,” which could return wrong results if 0 isn’t found.