Reference Formula via Index or VLookup?

I have two sheets. One is a database with a list of reps and their territory. I then have another database of customers. There is alot of turnover in the rep position so I would like the customer database to read the rep database's territory and spit out the rep name next to each customer. I have a territory column in both sheets. I know I'm close with either a Vlookup or Index formula. I think it needs to be an Index Match AND Index Collect formula somehow?

Since my sheets reference real people's names I don't want to share that publicly but it looks something like this:

Sheet 1:

[Rep] [Rep Territory] [Formula] (formula column is where I intend to put the formula)

Sheet 2:

[Rep Name] [Territory]

Best Answer

  • SteyJ
    SteyJ ✭✭✭✭✭
    edited 10/13/23 Answer ✓

    =IFERROR(INDEX({Rep Name}, MATCH([Rep Territory]@row, {Territory}, 0)), "")

    Place this where the formula column is in sheet 1, update the cross sheet references that are bolded above then convert to column formula. Note: If you have more than 1 rep per territory, you may want to use INDEX/COLLECT.


    Jacob Stey


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!