Forum Discussion

Gary_Bostock's avatar
Gary_Bostock
Copper Contributor
Nov 20, 2020

Formula error in link to another spreadsheet

I am trying to fetch data from one spreadsheet into another. no matter which lookup formula I use, I get an error:   There is a problem with this formula. Not trying to type a formula? When the f...
excel
Formulas and Functions
Bennadeau's avatar
Bennadeau
Iron Contributor
Nov 20, 2020

Hi Gary_Bostock,

Can you copy your formula here? It could help figuring out what your issue is.

Gary_Bostock's avatar
Gary_Bostock
Copper Contributor
Nov 25, 2020

Bennadeau =XLOOKUP(A47.'LISTINGS 2020.xlsx'!Code.'LISTINGS 2020.xlsx'!Description)

 

The formula is referencing cell A47 and is in a spreadsheet WCCOSTINGS, looking for source data contained in spreadsheet named LISTINGS 2020

 

Thanks

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 25, 2020

    Gary_Bostock 

    Are you sure you have dot as separator, not comma? I mean

    =XLOOKUP(A47,'LISTINGS 2020.xlsx'!Code,'LISTINGS 2020.xlsx'!Description)

instead of
=XLOOKUP(A47.'LISTINGS 2020.xlsx'!Code.'LISTINGS 2020.xlsx'!Description)
    • Gary_Bostock's avatar
      Gary_Bostock
      Copper Contributor
      Dec 02, 2020

      SergeiBaklan 

      Bennadeau 

      So I use the tool to do the formula

       

      And the result is

       

      =VLOOKUP(A14.'[LISTINGS 2020.xlsx]TIMBER'!$A$7:$A$147.'[LISTINGS 2020.xlsx]TIMBER'!$B$7:$B$147) which still throws up the same error.

       

      Is there not perhaps some keyboard or numeric setting in my program options, I really don't see how the template provided can go wrong?

      I have setup 2 clean spreadsheet to test the formula basically trying to do the same thing, VLOOKUP between spreadsheets and I am getting the same error.

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Dec 02, 2020

        Gary_Bostock If you provide your test spreadsheets it will be easier to explain what's wrong exactly. In any case formula in your post won't work

        =VLOOKUP(A14.   <= here shall be separator which is
                   comma or semicoln bu practically never dot
'[LISTINGS 2020.xlsx]TIMBER'!$A$7:$A$147.
                 <= the same (dot) and you take onlu one column
'[LISTINGS 2020.xlsx]TIMBER'!$B$7:$B$147)
                 <= for VLOOKUP here shall be column number, 
                    not the reference on the column.
Except dots that's XLOOKUP formula structure, not VLOOKUP.

         

  • Bennadeau's avatar
    Bennadeau
    Iron Contributor
    Nov 25, 2020

    Gary_Bostock 

     

    It seems like a simple vlookup would do the trick here.

    =VLOOKUP(A47,'[LISTING 2020.xlsx]Sheet1'!$A:$B,2,0)

    Change "sheet1" for the sheet name where your data is.

    Change "$A:$B" for where your table is.

    This reads: "Find the value located in A47 within column A of Listing 2020.xlsx, sheet1. Then return the value located right next to it (in column B).

    I hope this helps.

Resources