Feedback by UserVoice

 

How can we improve Excel for Windows (Desktop Application)?

← Excel for Windows (Desktop Application)

Add an If_NA optional sixth argument to XLOOKUP

The new XLOOKUP function introduced on August 28 2019 is awesome - exact match by default, the ability to return several columns at once, and search from bottom of data. But one thing would make it better - an optional sixth IfNA argument to provide an answer in case nothing is found.
One of the benefits of XLOOKUP is to eliminate the need to nest MATCH inside of INDEX. If you are trying to reduce the number of functions this would be a low-impact way of eliminating nesting XLOOKUP inside of IFNA.
For me, the main argument is that the IfNA thought process always comes at the end, after I've finished the heavy lifting of writing the XLOOKUP. It is an after-thought... "oh, I better handle the 0.1% of cases where I might get an error...". I always think of this near the end of the XLOOKUP, right about where the optional sixth argument would be. Instead, I have to do the keyboard shortcut dance of {Home}{RightArrow}ifna({End}).
The Excel Calc team added the awesome new Integer only arguments to RANDARRAY after it was in Insiders, so if this gets votes, consider doing it again! Thank you.

108 votes
Sign in
(thinking…)
Sign in with: Facebook Google
Forgot password?
Create a password
Signed in as (Sign out)
Close
Close

We’ll send you updates on this idea

Bill Jelen shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

17 comments

Sign in
(thinking…)
Sign in with: Facebook Google
Forgot password?
Create a password
Signed in as (Sign out)
Close
Close
Submitting...
An error occurred while saving the comment
  • [Deleted User] commented  ·   ·  Flag as inappropriate

    @George:

    Had that thought too when reading ththis suggestion. I imagine a lot of folks did.

    But having no match would be, by far, the most common error "in production" with a function of this kind. And testing particularly for that problem means having the calculation twice in a formula, complicating it, and having a small hit on performance for the times it does happen. (As in "if xlookup = error, then "", else xlookup.

    So having this particular error singled out seems like it would cut hugely down on complexity in the formulas so easier understanding and maintenance. And just less effort writing. The other errors really cannot approach this one's occurrence rate, even added together. I suspect they are actually not too common for anyone else either once outside the writing and vetting stage.

    So the particularity of having this error (only) dealt with inside the the formula itself seems pretty justified to me. And it reduces the ISERROR() testing to only needing to consider the other kinds of errors rather than have to test for this one separately and then lump the others together anyway, like one would using this, for handling.

    When thinking about it, I'd actually like all functions that need a match, but a match might not exist, to have this built in. Some can't fail, some kind of match will always occur, but I'd love to never have to trap this error for separate handling, on my own, again.

  • Abiola David commented  ·   ·  Flag as inappropriate

    Looking forward to when XLOOKUP will be made available for Office 365 Pro Plus users like me so that Excel users from Nigeria can feel the taste of the new gallant function. Can't wait 😀🇳🇬

  • AdamV commented  ·   ·  Flag as inappropriate

    @Mike Mellor - you know you can use IFNA(expression, value to return if expression is #NA), right?

  • Mike Mellor commented  ·   ·  Flag as inappropriate

    This belongs on all functions that can return NA. With large datasets, having to evaluate the expression twice is a big slowdown.

  • Anonymous commented  ·   ·  Flag as inappropriate

    I think it should be in 3rd and not Sixth to avoid adding 2 commas unnecessary

  • Wyn Hopkins commented  ·   ·  Flag as inappropriate

    I’d go with it as the 4th argument as it will be more commonly used than the other 2 optional parameters. Then you can avoid having to type extra 2 commas.....

    =XLOOKUP(A, Range1, Range2,,,”optional error handler “)

  • Francis Ogwal commented  ·   ·  Flag as inappropriate

    When it comes to looking staffs up, the percentage is very high that we would get a situation where some of the values are Not Available, adding IFNA as an argument can save a lot of time than putting the entire XLookup inside =IFNA, imagine your doing Xlookup very many times, i do agree with adding 6th argument IFNA in XLookup.

  • Mathieu Guindon commented  ·   ·  Flag as inappropriate

    I agree (and voted) because usability wins and this *would* be a usability win, but I'm not convinced it's a good idea from the dev/product perspective; it "pollutes" the function with concerns that aren't really its own, and then... it would introduce an[other] inconsistency in how things work, ...I mean, should every other function get an optional "value_if_error" parameter? No? Then why should XLOOKUP get it? What of XMATCH?
    Readability/maintainability-wise, if you see "=IFNA" in front of any formula then you know #N/A is handled. If there's a 6th argument instead, how long does it take an untrained eye to know #N/A is handled? The error handling gets obscured, and I don't think that's good.
    So... gets my vote, albeit with some serious reservations.

  • David Ringstrom commented  ·   ·  Flag as inappropriate

    XLOOKUP is going to democratize looking up data in Excel and adding this NA argument would close a gap to make this function into a superpower in Excel.

Excel for Windows (Desktop Application): Formulas and Functions

Categories

Feedback and Knowledge Base

(thinking…)