Reference Formula via Index or VLookup?

kwestmark

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]

SteyJ

=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.

SteyJ

=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.

kwestmark

WOW That was fast! thank you soooo much!

SteyJ

https://community.smartsheet.com/discussion/comment/399919#Comment_399919

Anytime 👍