Formula Question (VLookUp, INDEX, Collect?)

PugsRule
PugsRule ✭✭
edited 09/25/23 in Formulas and Functions

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

  • StevenBlackburnMBA
    StevenBlackburnMBA ✭✭✭✭✭

    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

  • PugsRule
    PugsRule ✭✭
    edited 09/25/23

    Hi @StevenBlackburnMBA

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!