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

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

=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

WOW That was fast! thank you soooo much!

Help Article Resources
Categories
Check out the Formula Handbook template!