Formula Question (VLookUp, INDEX, Collect?)

PugsRule

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

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.

PugsRule

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)