Using VLOOKUP to populate multi-choice selections

Darrell Stout

Smartsheet Community,

I have a unique problem. I am trying to pull in data from Sheet B (Hazard's) when multiple choice answers are selected on the Work Instruction sheet. The idea is for someone to select all the hazards for that step, then the "hazard eliminations" that are associated with that "hazard" will show. I can only get one of the hazards to show a hazard elimination. It will not let me select multiple hazards and show multiple hazard elimination.

Here is my formula: =VLOOKUP([Sub-Component]@row, {Hazards/Hazard Elimination All Columns}, 2, false)

Please see attached photos

Jason Tarpinian

I don't think you can do that with one lookup formula, because it's technically looking for all of those values combined, not each one individually. A workaround I can think of is having helper columns on your Wind WI sheet for each possible hazard and make the formula

=IF(HAS([Sub-Component]@row,"Slips, Trips, Falls"),[INSERT YOUR LOOKUP FORMULA FOR "SLIPS, TRIPS, FALLS" HERE])

Make a helper column similar to that for each hazard. Then, in your Column5, use the JOIN function to bring all of the helper columns together.