Today, November 3, 2019, the function arguments for XLOOKUP changed. The if_not_found argument moved from 6th to 5th. That means any old XLOOKUP functions that used arguments 4, 5, or 6 might break at the next Recalc.
Transcript of the video:
Learn Excel from MrExcel Podcast Episode 2290.
Check your XLOOKUP functions. Hey, welcome back to MrExcel Netcast. I'm Bill Jelen.
This is only for Office Onsiders. If you've been an early adopter using XLOOKUP until November 2nd, 2019 or earlier. You got to watch this video. Because today, November 3rd, 2019, I downloaded a new version of Excel and the signature for XLOOKUP has changed. The thing that used to be 4th is now 5th. The thing that used to be 6th is now 4th.
What does that mean?
That means that yesterday when this XLOOKUP hey, I want the exact match or the next larger item. Well, it still right when I open the workbook, but as soon as I allowed this to recalculate by changing the table, let's say from 59 to 60, press Enter and watch all of those results change. What's going on? While the formula that used to say Match_mode was fourth is now saying if not found, and because I didn't put in match mode in I'm now default into a match mode of Exact and there is no Exact, so now it's defaulting to the if not found.
Well I could be upset about this, but simple fact? Is's Office Insiders were using a pre- release version of Excel and they're allowed to change that until the real version comes out. I remember back when used to sign up for a beta, they would tell you no production work here. I tend to forget about that and use it for my production work. Have you been using XLOOKUP and you've been using the 4th, 5th, or 6th argument you want to go check your spreadsheets all? Get all the spreadsheets, you sent out to people. Pull them back in. Edit the formula and fix it. I mean, it makes sense to have, if_not_found as the 4th argument.
I get it. It will be great for the greater good as it rolls out to general availability whenever that happens, but for anyone who was an early adopter, go check this out and make sure that your XLOOKUPs are still correct.
Thanks for stopping by. We'll see next time for another netcast from MrExcel.
Check your XLOOKUP functions. Hey, welcome back to MrExcel Netcast. I'm Bill Jelen.
This is only for Office Onsiders. If you've been an early adopter using XLOOKUP until November 2nd, 2019 or earlier. You got to watch this video. Because today, November 3rd, 2019, I downloaded a new version of Excel and the signature for XLOOKUP has changed. The thing that used to be 4th is now 5th. The thing that used to be 6th is now 4th.
What does that mean?
That means that yesterday when this XLOOKUP hey, I want the exact match or the next larger item. Well, it still right when I open the workbook, but as soon as I allowed this to recalculate by changing the table, let's say from 59 to 60, press Enter and watch all of those results change. What's going on? While the formula that used to say Match_mode was fourth is now saying if not found, and because I didn't put in match mode in I'm now default into a match mode of Exact and there is no Exact, so now it's defaulting to the if not found.
Well I could be upset about this, but simple fact? Is's Office Insiders were using a pre- release version of Excel and they're allowed to change that until the real version comes out. I remember back when used to sign up for a beta, they would tell you no production work here. I tend to forget about that and use it for my production work. Have you been using XLOOKUP and you've been using the 4th, 5th, or 6th argument you want to go check your spreadsheets all? Get all the spreadsheets, you sent out to people. Pull them back in. Edit the formula and fix it. I mean, it makes sense to have, if_not_found as the 4th argument.
I get it. It will be great for the greater good as it rolls out to general availability whenever that happens, but for anyone who was an early adopter, go check this out and make sure that your XLOOKUPs are still correct.
Thanks for stopping by. We'll see next time for another netcast from MrExcel.
