Formula Question (VLookUp, INDEX, Collect?)
Any suggestions on a formula that will work for this? I have tried and tried, but nothing I come up with works. Using the Emp# on Sheet 1, I would like it to look at Sheet 2 for that # in the column "Hunt Group and Fax Access" and if found return True on Sheet 1 in column "Hunt Group/Fax Lookup".
Answers
-
Hi @PugsRule -- you would want to index match this specifically. Is there a reason that the 3 items are all in the same cell? Index Contains would make sense here if you absolutely have to bring the data in that way, but its far more of a complex formula and believe you may be able to tighten up the intake/data organization to make this easier for yourself.
Certifications:
-Smartsheet 2023 Core Product Certification
-Smartsheet 2023 System Administrator Certification
-
It has to contain multiples, some rows will have up to 25 in them. There isn't a way around it. The only change I could make is to not have number, but just the numbers if that would help. Can you spell out the forumula? I have tried that many ways to no avail - just a few:
=INDEX(COLLECT({[Emp #]@row}, {Master Phone Handling 2023 Range User}, CONTAINS([Emp #]@row,@cell)),1)
=INDEX(COLLECT({[Emp #]@row}, {Master Phone Handling 2023 Range User}, CONTAINS([Emp #]@row)),1)
=INDEX(COLLECT({[Emp #]@row}, {Master Phone Handling 2023 Range User}, CONTAINS([Emp #]@column)), 1)
=INDEX(COLLECT({Emp #}, {Master Phone Handling 2023 Range User}, CONTAINS([Emp #]@row, @cell)), 1)
=INDEX(COLLECT({Emp #}@row, CONTAINS({Master Phone Handling 2023 Range User}@row,@cell)1)true)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!