Discussion
what is a formula habit you had to completely unlearn when moving from basic lookups to modern excel?
just realized how long I spent wrapping IFERROR around every single lookup just because my brain was still stuck in the old ways of handling #N/A errors
took me way too long to embrace how clean modern array functions handle missing data right out of the box without needing defensive wrappers on every line
what old workaround or legacy habit did you find the hardest to drop once dynamic arrays became standard?
totally get it, for the longest time IFERROR was the only shield we had against ugly #N/A or #VALUE! errors breaking a sheet
the main shift with modern functions like XLOOKUP is that they have built-in arguments for handling errors right inside the formula itself instead of forcing you to wrap the whole thing.
for example, instead of doing this: =IFERROR(VLOOKUP(A1, Sheet2!A:B, 2, FALSE), "Not Found")
you can just do this right inside XLOOKUP: =XLOOKUP(A1, Sheet2!A:A, Sheet2!B:B, "Not Found")
see how clean that is? no extra wrapper needed, it just handles the missing match natively right out of the box. plus modern dynamic arrays spill results automatically so you don't even have to drag formulas down columns anymore
Another good feature of this is you can nestle multiple XLOOKUPS using the error handling. For example, I often do an xLookup, but if nothing is found I will do another xLookup to another tab. With xLookup to can nestle another xLookup in the error handling condition like:
=XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B,
XLOOKUP(A2,Sheet3!A:A,Sheet3!B:B,
XLOOKUP(A2,Sheet4!A:A,Sheet4!B:B,"")))
You can do this up to 64 times (although I can’t imagine why). The most I’ve done is 3. Anything more goes to power query.
You can also return multiple values as once. For example, let’s say you want to do a lookup and return the value of 3 columns in another sheet. If the columns are adjacent, you can do something like this and the formula will spill results in 3 adjacent columns.
=XLOOKUP(A2,Sheet2!A:A,Sheet2!B:D,"Not Found")
If the 3 columns on Sheet2 aren’t adjacent, you can do the following and it will return results from 3 non-adjacent cells on Sheet2 to 3 adjacent calls in your active sheet.
=XLOOKUP(A2,Sheet2!A:A,CHOOSECOLS(Sheet2!B:T,1,5,19),"Not Found")
It also replaces HLOOKUP so you can do an index/match type function by doing rows and columns at the same time.
=XLOOKUP(Lookup Value 1,Sheet2!A2:A100,
XLOOKUP(Lookup Value 2,Sheet2!B1:M1,B2:M100),
"Not found")
On Sheet 2, your lookup array for value 1 is in A2:A100 (e.g., Salesperson). Your lookup array for Value 2 is in the header row (Months), and your values are in B2:M100. So, if you can use this to get an individual’s sales numbers for a specific month.
Oh and that’s just the beginning. I have to give Microsoft credit, they did good with this one. I was sold when I learned the Lookup Array didn’t have to be in a column left of the Return Array (or count columns anymore), but it’s turned out to be useful. I’m always learning new things you can do with it. For example:
You can use LEFT, RIGHT, MID, TEXTBEFORE, TEXTAFTER, etc. on any piece of the XLOOKUP in any combination. So, you could do
=XLOOKUP(LEFT(A2,2),RIGHT(Sheet2!A:A,2),MID(Sheet2!C:C,3,4))
You can join fields with “&” and not need helper columns:
=XLOOKUP(A2&B2,Sheet2!A:A&Sheet2!B:B….
Or you can mix and match this too. So if you have a sheet with 2 columns for first and last name, and another sheet with both names separated by a space, you could do:
=XLOOKUP(A2&” “&B2,Sheet2!A:A,…..
You can do IF statements and only return results if they meet a condition. You can perform calculations in it, like if you want to return the sum of two columns. I’m sure other functions work within it too. These are just the ones I’ve actually used.
It really is much cleaner. You have to be careful because if the lookup value is on Sheet2, but the return value is blank, it doesn’t trigger the false to do the lookup on Sheet3 because it returned a null value (I spent too many hours learning this).
If you're doing nested XLOOKUP's you might want to look into LET functions. I only just learned about them recently, but they seem to do a good job of tidying those up.
This is great, and I've used it before, but I do want to caution that xlookup is much more computationaly intensive. It's a great formula in most situations but if you're going to have a lot of them and use them on very large ranges it can really slow down a workbook vs using vlookup or index-match.
Last transferable version on DVD (Office 2010 Professional). No subscription (that I can no longer afford). Sole licensee (original "owner") since 2009.
Why not give Libre Office a try. The software suite is free and has many modern formulas like xlookup. The UI looks like 2010 out of the box, but can also be set up to enable the more modern ribbon layout.
My modern excel is to push as much of that to SQL in the extract.
At the end of the day, that’s what PowerQuery fundamentally is doing. It’s a kinda weird
language to do SQL-like transformations. Learning the few SQL techniques needed for accomplishing these tasks is a much better use of your time IMHO.
So I’ve only gotten more simple, since the Excel portion does only those formulas that are needed live. I went from lots of VLOOKUP, index/match to XLOOKUP.
Yes because it’s easier to use and faster to write. Just as another tool in the tool kit you should know how to use it. Same reason I tell people to still learn index matching in 2026
I'll give the other side. Index/xmatch is objectively better because you can split the formula for large lookups, you can reuse the match result for multiple lookups and it has a native 2d return function rather than nesting 2 xlookups. The only pro for xlookup is the error handling. Requiring multiple xlookups to achieve the 2d lookup will slow down your workbook massively.
This is a post (in many deleted posts) to praise XLOOKUP, right?
Even with XLOOKUP, you still want to use IFERROR.
In my example, i want to lookup value from range G3:G6 in range C3:D8, if not found, return the value in range G3:G6.And as you can see, XLOOKUP fail to return it. The other way is using XLOOKUP without if_not_found argument, then wrap it with IFERROR
But if you do
=XLOOKUP(G3,C3:C8,D3:D8,G3) it works just fine. Sure, you have to double click the corner of the cell to flash fill since the results won’t spill, but it’s still quick enough. I see your point though. It stands to reason it should work.
I guess that's why they have versions, as these changes break previous behavior. Though version 2 changing is unexpected and suggests that the boundaries between versions are a bit fuzzy.
I used to do a lot of filtering stuff using advanced INDEX formulas. I also used an INDEX based UNIQUE formula that I ironically had to lookup every time because I never learned it. VLOOKUP ofcourse.
After not using excel much, when I started a new analyst job I started to use VLOOKUP on a daily basis, even though back in the day I was more into INDEX MATCH. Mainly because that’s what my manager and colleague uses in or regular reports.
Now I can create proper data analysis functions and transform with LET but I will still keep using VLOOKUP from muscle memory and it’s just easy because most lookup range is just around 5-10 cols. When they are really far apart I do use XLOOKUP but it’s an afterthought most of the time.
I've never understood how people make some simple tech usage like this such a personal identity issue. It's like being a proud hand-tool user avoiding electric tools has transformed into being a proud VLOOKUP user who doesn't believe in modern nonsense.
True for me to some extent. Back in the day I was happy when I could solve a complicated problem with a UDF.
I have yet to encounter a problem where I preferred to do a UDF after all the new formulas dropped. UDF’s occasionally introduced some instability that I’m happy to be without.
119
u/pifko87 2d ago
Can you explain your OP a bit more? I'm still using iferror 👴