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.

    Index formulas replacing VLookUp Formulas

    ✭✭✭✭✭
    in Formulas and Functions

    Hello

    It has been recommended that I replace my vlookup formulas with Index(Collect) formulas. This is my first experience with the Index and / or Collect Match concept and honestly I am struggling on my first try. I am trying to pull in a total funds approved value from the my budget sheet to my portfolio sheet.

    Both Sheets start with a column title OPS# - this would be the matching data value and I want to pull the value from the column/cell match "Total Funds Approved". The are name the same on both sheets.

    English statement (to the best of my ability) building the formula in the column titled "Total Funds Approved" on the Operations Project Log. Find the matching OPS# from the Operations Project Log on the Funds Approval Log in Column titled "OPS#. If match found then pull to the Operation Project Log the value that is in the column titled "Total Funds Approved" on the Funds Approval Log to the Operations Project Log.

    Looking for a little help with this learning curve.

    Thank you

    Dawn

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answers

    • ✭✭✭✭
      Answer ✓

      You only need Collect() if you need to use an arithmetic function as part of your formula.

      If you only need to lookup a single value, you can use something like this:

      =INDEX(OPS#:Total Funds Approved,(MATCH([OPS#]1,OPS#:OPS#,0),2)

      This assumes that the Total Funds Approved column is 1 row to the right of the OPS# column.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭
      edited 04/19/22 Answer ✓

      The last part of the formula is the column index reference. So if your range is 21 columns across, and the value of interest is in the 21st column (your Total Funds Approved value), the last number needs to be 21.

      =INDEX(OPS#:Total Funds Approved,(MATCH([OPS#]1,OPS#:OPS#,0),21)

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭
      Answer ✓

      You only need Collect() if you need to use an arithmetic function as part of your formula.

      If you only need to lookup a single value, you can use something like this:

      =INDEX(OPS#:Total Funds Approved,(MATCH([OPS#]1,OPS#:OPS#,0),2)

      This assumes that the Total Funds Approved column is 1 row to the right of the OPS# column.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      @DW1 What is the impact if the "Total Funds Approved" column is 20 columns to the right?

      · Share on FacebookShare on Twitter
    • ✭✭✭✭
      edited 04/19/22 Answer ✓

      The last part of the formula is the column index reference. So if your range is 21 columns across, and the value of interest is in the 21st column (your Total Funds Approved value), the last number needs to be 21.

      =INDEX(OPS#:Total Funds Approved,(MATCH([OPS#]1,OPS#:OPS#,0),21)

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      @DW1 - That work great! Thank you so much for your assistance.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      @DW1

      Because you were so helpful on my question regarding Index(Match) formulas. I need a little more help. Today my Index(Match) formula is erroring and the only change I can identify is that I am using a new set of sheets. When I create the formula it is adding an additional parenthesis at the end of the formula that does not exist in previous.

      Here is the details

      Sheet 1 - OPS Tracker

      Sheet 2 - Cap Request Log. This is the sheet where I am creating the Index(Match) formula

      The goal is to find the OPS number from the Cap Request Log on the OPS Tracker and return the Project Phase. If no match found than leave blank.

      OPS # is the first column on both sheets. Project Phase is column number 26 on the Ops Tracker. Column Names do not mirror each other on both sheets (Ops # vs Ops Number)

      Here is the formula as I have it written.

      =IFERROR(INDEX({2022 OPS Project Track - Index for Prj Phase}, MATCH([OPS Number]@row, {2022 Operations Project - OPS#}, 0, 26), ""))

      Results = #Incorrect Argument Set


      Help?

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      @DW1 - I actually finally figured it out. YAY

      · 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

    • Formula Help

      I'm trying to create a SUMIF formula that looks at the salesperson name in a column and adds up or totals their $ sales in another column. To ultimately show in Dashboard of Totals Sales by Salesperso…
      User: "Allan Z"
      Answered ✓
      Formulas and Functions
      9
      2

    • Unparseable Error

      Good day Smartsheet Team, Getting an unparseable error on this formula: =IF($Name@row <> "",(SUMIFS({Expense}, {Period},1, {Type}, OR(@cell = "RES602782", @cell = "RES602497")),"") Trying to pull in a…
      User: "stratman"
      Answered ✓
      Formulas and Functions
      15
      2

    • Formula to return the non-blank cell

      I have a sheet that compiles all the responses from a form. The sheet has multiple start and end date columns, but only one start and one end date cell is NOT blank depending on the activity selected …
      User: "m_anderson"
      Answered ✓
      Formulas and Functions
      13
      2