+ Reply to Thread
Results 1 to 9 of 9

XLOOKUP returning #N/A error

  1. #1
    Registered User
    Join Date
    10-07-2023
    Location
    Manama. Bahrain
    MS-Off Ver
    365
    Posts
    1

    XLOOKUP returning #N/A error

    Dear ExcelForum users,

    I'm encountering a problem with XLOOKUP in my data where some values match while others do not, even though those values exist in the lookup array. I have attached a sample Excel file for reference.

    Specifically, I am using the value in cell C4 from my source file to perform an XLOOKUP on another Excel file to retrieve corresponding values. However, XLOOKUP returns an #N/A error. When I press F2 and then Enter on cell C4, XLOOKUP works correctly. The issue is that I cannot identify any difference in the value in cell C4 before and after pressing F2 and Enter.

    I have also tried using the VALUE formula on cell C4, but it doesn't resolve the issue. Applying the NUMBERVALUE formula or combining VALUE with the CLEAN formula on cell C4 makes XLOOKUP work properly.

    My questions are:

    What is the actual difference between the NUMBERVALUE formula and the VALUE formula?
    Why does XLOOKUP fail when there appears to be no visible difference in cell C4 before and after pressing F2 and Enter?

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,790

    Re: XLOOKUP returning #N/A error

    Welcome to the forum.

    Sometimes, and there seems to be no rhyme or reason to it, data entered into a column will be entered as text. It's most noticeable when using formulae. The only way to correct it is to do what you mentioned with F2 and ENTER.

    The ONLY other way round it (that doesn't involve deleting the values, setting cells to GENERAL formatting and reentering them) is to use one of the workaround formulae you've found.

    It's one of those 'undocumented features' that you have stumbled across.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: XLOOKUP returning #N/A error

    Problem Is with C4.
    If such problem is with range say C4:C10
    Select C4:C10
    Data--> text columns-->Next--> Next --> Finish.
    I tried for C4 . It is working.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: XLOOKUP returning #N/A error

    But do you know the root cause? It's not formatted as text, there are no non-printing characters.... Data/Text to columns is doing the same as CTRL-F2... isn't it??
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,870

    Re: XLOOKUP returning #N/A error

    It looks as though C4 was originally a calculated value & is not exact, if you put this in another cell
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    it shows a small residual value

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: XLOOKUP returning #N/A error

    I really could not understand the cause.

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: XLOOKUP returning #N/A error

    I increased the decimal value up to 12 decimals, found no change of value.

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,870

    Re: XLOOKUP returning #N/A error

    That's because Xl only shows numbers to 15 significant digits.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: XLOOKUP returning #N/A error

    Ha! That's it... I opened up the dps in both cells to about 20 or so, saw no difference, and then went off to try and find an alternative explanation!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. xlookup - returning N/A
    By jozoo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-08-2023, 06:06 AM
  2. Replies: 6
    Last Post: 03-11-2023, 10:13 AM
  3. Help with XLOOKUP returning multiple values
    By Schuck in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-16-2022, 06:21 AM
  4. Xlookup returning value based on date
    By nmarcon in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-28-2022, 02:16 AM
  5. [SOLVED] xlookup not returning value due to looking up dates
    By dharvey1978 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-08-2022, 03:11 AM
  6. XLOOKUP not finding/returning matches on X & Y axis.
    By JamesParkerDC in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-12-2022, 12:13 PM
  7. [SOLVED] XLookup returning wrong values when using two conditions
    By Einrastor in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-03-2020, 07:38 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1