web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / #N/A Error in excel fr...
Microsoft Dynamics NAV (Archived)

#N/A Error in excel from export using Configuration Package NAV 2013 R2

(0) ShareShare
ReportReport
Posted on by Cathy Burn 65

I am wanting to update the email addresses in the Customer Table.  I have exported the Customer table using the configuration package into excel.  I have copied the result into a new excel spreadsheet (S1) using , Paste, Values.

I am using vlookup to a separate spreadsheet (S2) to populate the email address column in (S1)

The result is #N/A in the email field (S1)

Both spreadsheets are formatted as general and are sorted by column A

I have tested the vlookup function between worksheets within (S2) and it works, but the same test within (S1) returns the #N/A error.

Has anyone resolved this error please.

*This post is locked for comments

I have the same question (0)
  • Tobias Strobelt Profile Picture
    Tobias Strobelt 152 on at

    Hi Cathy,

    can you please give an example of your VLOOKUP syntax and the column layout you have in your S2 spreadsheet? I´ve tried the same and I get the correct email results. My VLOOKUP looks as following:

    Spreadsheet S2:

    No.               Email
    01121212     spotsmeyer's.furnishings@cronuscorp.net
    ....

    =VLOOKUP(A2;Table35;52;FALSE)

    whereas Table35 would represent the full customer package table in S1 excluding columns and column 52 is the [email] column.

    Best regards,
    Tobias

  • Cathy Burn Profile Picture
    Cathy Burn 65 on at

    Hi Tobias, thanks for your response.

    I have now been able to populate my email addresses into in S1 spreadsheet by VLOOKUP on Column 2, which is the Customer Name field.

    However I now get an error when trying to import the spreadsheet

    ' Excel does not contain XML mappings.'

    It would be more correct to use the original exported spreadsheet from NAV as this will contain the XML mapping, however the formatting of this spreadsheet is such that you cannot perform a VLOOKUP.

    for example looking up column 1 does not return the customer number but  [@[No.]]

    =VLOOKUP([@[No.]],'[Customer.xlsx]Sheet1'!$A$4:$C$7,2,false)

    [Customer.xlsx]Sheet1 - is S2

    $A$4:$C$7 - is the array

    Column 2 contains the email address

    We have re-installed NAV2013 in April, so am only just getting used to it.  Previously had V5.1 and used dataports.

    Am I using the correct method to update the email addresses in the customer table?

  • Cathy Burn Profile Picture
    Cathy Burn 65 on at

    Hi Tobias - further progress.  I have now managed to import my file.

    I used the original export excel file.  VLOOKUP would not work if I selected the fields using the function wizard.  I had to manually enter the syntax.

    I also had to use Column B for the Lookup_value.

    This is not the best option as in my case the field contained the customers name, the export file and the lookup spreadsheet were from different sources and may not have been identical.

    Using column A which is the customer number and primary key would be a more accurate option.

    If you know how to use column A as the lookup_value in VLOOKUP, within the original exported excel file.  I would be most grateful.

    Regards

    Cathy

  • Suggested answer
    keoma Profile Picture
    keoma 32,729 on at

    hi

    why do you use vlookup to get the values from the email column.

    best way is simple: Table_S2_Cell1 = Table_S1_EmailCell

    then copy this down the column in table s2.

    for function vlookup check:

    blogs.office.com/.../solutions-to-three-common-problems-when-using-vlookup

    stackoverflow.com/.../excel-failing-at-string-vlookup

    stackoverflow.com/.../vlookup-text-inside-a-string

    best regards

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics NAV (Archived)

#1
HoangNam Profile Picture

HoangNam 7

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans