VLOOKUP Formula?

Raquel Charaba

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.

I appreciate any help you can provide.

heyjay

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, 

""))))

Raquel Charaba

@heyjay it worked! thank you so much!

heyjay

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, 

""))))

Raquel Charaba

@heyjay it worked! thank you so much!