Equivalent to Excel's =XLOOKUP?

Greetings! Very, very very new to Smartsheet. Trying to evaluate whether it can replace a Sharepoint Excel tracker I use at my current company. Basically, we have ~10 (give or take a few) people working on the tracker at any given time, updating it in real-time to log incoming shipments and their details. I have an XLOOKUP formula that automatically fills in customer account numbers and internal account managers as soon as the catalog number is entered.

I.E., receiver enters in cell B2 the catalog number: 123456, cells C2 and D2 then automatically populate with the account number and account manager respectively. I have a reference table in another worksheet on Excel that the XLOOKUP formula works with. I know there are no worksheet tabs with Smartsheet, but I have created a new Smartsheet that I would like to link. It looks like an =INDEX() and linking to the reference sheet should do the trick, but I keep getting #INCORRECT ARGUMENT. Is there a tutorial or anything anywhere for this specific function?

How does Smartsheet run when there are up to 10 people actively maneuvering inside and dealing with thousands of rows of data?

Sorry if this is not the right forum for this or if my question is too broad. Feel free to delete this if so. Again, new to this and just trying to find which resources are where! 😁


  • Hollie Green
    Hollie Green ✭✭✭✭✭
    edited 10/28/22

    Index/collect combination should work

    In Account Manager column on your reference sheet reference the account manager on your output sheet and the Catalog Number in each sheet. those in {} are on your reference sheet those in [] are in your output sheet. You will have to create the references so can't just copy and paste the formula

    =Index(Collect({Account Manager},[Catalog Number]@row,{Catalog Number}),1)

  • Hollie Green
    Hollie Green ✭✭✭✭✭

    Sorry for your other question I have had good luck with multiple user in the same sheet simultaneously. I have about 20 in my sheet at any given time.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!