Forum Discussion

jkabush's avatar
jkabush
Copper Contributor
Oct 24, 2019

VLookup formula error only in Excel 2016 not 2019

I have a new SQL administrator who is recreating a customer database to work with our custom building software. In so doing she is using VLOOKUP heavily in the excel spreadsheet. The problem comes in that we have a mix of Office 2016 and Office 2019 and not really the budget to upgrade dozens of computers at the moment (we don't qualify for a site license).
 
The following formula (minus the spaces as it won't post with spaces) returns a #NAME? error saying "The formula contains unrecognized text" in the Excel 2016 versions only. Excel 2019 displays as expected.
 
= VLOOKUP ( [ @ Job ] , Customers ! A : C , 3 , 0
 
Is there a discernible error in this formula or a better way to write it? Is there a setting that may be different in 2016 vs 2019? I am not enough of an Excel expert to figure this one out (I already made sure the columns are set to "text" as the Office FAQ page suggests).
 
This SQL Admin's solution is to just upgrade all the machines which is not in our budget.
admin
excel
Formulas and Functions

3 Replies

  • Celia_Alves's avatar
    Celia_Alves
    MVP
    Oct 24, 2019

    jkabush 

    Hi, there! I am not sure what could cause this, but we can try finding the cause. Whatever it is, I am quite sure that an error with the VLOOKUP formula is no reason for having to upgrade your Office version.

     

    There is a parenthesis missing at the ens of the formula but I am assuming that is just a typo here.

     

    Are you testing with the exact same file on both computers?

     

    The portion "[ @ Job ]" is referring to what is in column "Job" on a table in the same row where the formula is. Do you have an excel table in both situations? If you don't know what an Excel Table is, please see the video below.

     

    https://www.youtube.com/watch?v=Qk7bChbIefg&t=43s

     

    Excel tables are special entities in Excel and they should work about the same way in Excel 2016 and 2019. My feeling is that in one of the cases, your excel table was converted to range.

    Please let me know if this is of any help. Good luck!

     

    • jkabush's avatar
      jkabush
      Copper Contributor
      Oct 24, 2019
      The parenthesis seems to have been eaten in posting, yes.

      The values are being pulled from a separate table in another tab of the file.

      How do I check to see if that has been changed to range?
      • Celia_Alves's avatar
        Celia_Alves
        MVP
        Oct 24, 2019

        jkabush 

        I recommend watching the video. Knowing how to work with tables is not difficult and makes all the difference in what you can achieve with Excel.

         

        If the formula is on a table when you click on the cell a new tab called "Table Design" at the top of your ribbon should appear. That tab does not appear when you are using a range and not a table.

         

Resources