Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Please help me troubleshoot this vlookup formula cross referencing another sheet

    ✭✭
    in Formulas and Functions

    Hi,

    Please help me identify the error in this formula:

    =VLOOKUP(Barcode@row, {Main List!Barcode:Item Name}, 2, false)

    I'm trying to use the formula in a sheet called "Equipment Tracker" to pull in the Item Name column using the Barcode to reference the correct Item Name in another sheet called "Main List".

    In both sheets, the column Barcode is the Primary column and Item Name is column B.

    Thanks!

    image.png

    image.png
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • Community Champion
      Answer ✓

      That makes sense. The problem will be one of the references. Probably the cross sheet reference. Highlight the part in bold:

      =VLOOKUP(Barcode@row, {Main List!Barcode:Item Name}, 2, false)

      Delete it.

      Then leaving the cursor where it was, create a new reference.
      You need to select the two columns you show above and give the range a name (I would avoid ! And : in the name. Save that reference.
      The formula will update to show your new reference in place of the part in bold.

      That could be all that is needed.

      Good luck!

      This page explains criss sheet formula
      https://help.smartsheet.com/learning-track/level-3-advanced-users/cross-sheet-formulas

      · Share on FacebookShare on Twitter

    Answers

    • Community Champion

      Hi @kmccoy1

      Your formula looks good. Can you tell me what the error is?

      · Share on FacebookShare on Twitter
    • ✭✭

      Hello,

      I keep getting this error: #INVALID REF

      · Share on FacebookShare on Twitter
    • Community Champion
      Answer ✓

      That makes sense. The problem will be one of the references. Probably the cross sheet reference. Highlight the part in bold:

      =VLOOKUP(Barcode@row, {Main List!Barcode:Item Name}, 2, false)

      Delete it.

      Then leaving the cursor where it was, create a new reference.
      You need to select the two columns you show above and give the range a name (I would avoid ! And : in the name. Save that reference.
      The formula will update to show your new reference in place of the part in bold.

      That could be all that is needed.

      Good luck!

      This page explains criss sheet formula
      https://help.smartsheet.com/learning-track/level-3-advanced-users/cross-sheet-formulas

      · Share on FacebookShare on Twitter
    • ✭✭

      THANK YOU! This fixed it and I learned something :)

      I appreciate your help!

      · Share on FacebookShare on Twitter
    • Community Champion

      Wonderful. Thanks for letting me know.

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions