Reference Formula via Index or VLookup?

kwestmark
kwestmark ✭✭✭
in Formulas and Functions

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]

· Share on FacebookShare on Twitter

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.

    Sincerely,

    Jacob Stey

    · Share on FacebookShare on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!