Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

VLOOKUP Formula?

Hello,

I need help with creating a formula that captures two criteria:

1- It assigns a score based on the type of incident.

2- If multiple incidents are logged in a single cell (via a multiple-choice dropdown), the score assigned will correspond to the incident with the lowest weight. For example, if two incidents are logged, such as First Aid and Record Only, the formula will score for First Aid, as it has the lower score.


Please note that having a single dropdown for this column and logging additional incidents in separate columns is not a viable option for us.

2024-08-01_08-25-30.png incident typePNG.PNG

I appreciate any help you can provide.

Tags:

Best Answers

  • ✭✭✭✭✭
    Answer ✓

    Can you try this?

    =IF(HAS(
    [Incident Type]@row, "recordable"), 1, 
    
    IF(OR(HAS(
    [Incident Type]@row, "Environmental Release"), 
    HAS([Incident Type]@row, "First Aid"), 
    HAS([Incident Type]@row, "Vehicle Incident")), 2, 
    
    IF(OR(HAS(
    [Incident Type]@row, "Property Damage"), 
    HAS([Incident Type]@row, "Subcontractor Incident")), 3, 
    
    IF(OR(HAS(
    [Incident Type]@row, "Near Miss"), 
    HAS([Incident Type]@row, "Personal Medical"), 
    HAS([Incident Type]@row, "Record Only"), 
    HAS([Incident Type]@row, "Regulatory Agency Visits")), 4, 
    
    ""))))
    

    ...

  • ✭✭✭✭
    Answer ✓

    @heyjay it worked! thank you so much!

Answers

  • ✭✭✭✭✭
    Answer ✓

    Can you try this?

    =IF(HAS(
    [Incident Type]@row, "recordable"), 1, 
    
    IF(OR(HAS(
    [Incident Type]@row, "Environmental Release"), 
    HAS([Incident Type]@row, "First Aid"), 
    HAS([Incident Type]@row, "Vehicle Incident")), 2, 
    
    IF(OR(HAS(
    [Incident Type]@row, "Property Damage"), 
    HAS([Incident Type]@row, "Subcontractor Incident")), 3, 
    
    IF(OR(HAS(
    [Incident Type]@row, "Near Miss"), 
    HAS([Incident Type]@row, "Personal Medical"), 
    HAS([Incident Type]@row, "Record Only"), 
    HAS([Incident Type]@row, "Regulatory Agency Visits")), 4, 
    
    ""))))
    

    ...

  • ✭✭✭✭
    Answer ✓

    @heyjay it worked! thank you so much!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions