Best way to look up multiple values in a multi-select cell?

Michael McKenna

I have a schedule that contains tasks, and each task has one or more cost codes associated with it. These cost codes are in Dropdown (multi select) cells. For each cost code in a cell, I would like to look up the associated value and add the values together if there are more than one. I am not sure the best way to do this.

I'll weight the total duration to come up with an approximation later, for now I am just trying to get the corresponding budget numbers to add.

Thanks!

Andrée Starå

Hi Michael,

How many codes can be selected on each row?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Sam Phyall

I am looking for something very similar that has me scratching my head. To answer Andree for me it could be up to 10!

Michael McKenna

Hi Andreé,

in the example picture there are two distinct cost codes in a cell. There is typically always at least one and there are a few instances where there are seven or eight cost codes in a cell. Thanks for looking into this!

Michael

Paul Newcome

You could use something along the lines of...

=SUMIFS({Other Sheet Budget Column}, {Other Sheet Cost Code Column}, CONTAINS(@cell, [Cost Code]@row))

Andrée Starå

@Michael McKenna

Happy to help!

I saw that Paul answered already!

Let me know if I can help with anything else!

Best, 

Andrée 

CaryS

I'd suggest one minor tweak to Paul's formula and that would be to use HAS instead of CONTAINS. In looking at your screenshot I see there's one cost code that is "05-". Contains would include its corresponding budget value any time the multi-select cost code column has a code with "05-" in it. HAS on the other hand would look for an exact match. The has formula would look like this:

=SUMIFS({Other Sheet Budget Column}, {Other Sheet Cost Code Column}, HAS([Cost Code]@row, @cell))