Multiple IF Criteria across multiple columns

[Melo]

Hi All,

Please forgive me if I do not explain myself clearly; I am rather new to Excel. I am trying to create a formula that will populate a cell with text depending on an entry in 7 other columns. Only one of the seven columns will have an "Yes" entry. This is what I have so far that is not working:
=IF(C:C="Yes","Injury",IF(D:D="Yes","Illness",IF(E:E="Yes","Property Damage",IF(F:F="Yes","Motor Vehicle",IF(G:G="Yes","First Aid",IF(H:H="Yes","Near Miss",IF(I:I="Yes","Other")))))))

So if there is a "yes" in column C, I want "Injury" to be displayed in my destination cell; if column C is blank but there is a "yes" in column E, then display "Property Damage" and so on. I am completely flummoxed. Any help would be greatly appreciated!!

Melo

 

[Marcelo Branco]

Hi and welcome to Mr Excel Forum

Assuming there are headers in row 1 like below

B C D E F G H I
[TABLE="class: grid, width: 850"]
<tbody>[TR]
[TD="width: 115, bgcolor: transparent"]Result
[/TD]
[TD="width: 64, bgcolor: transparent"]Injury
[/TD]
[TD="width: 64, bgcolor: transparent"]Illness
[/TD]
[TD="width: 115, bgcolor: transparent"]Property Damage
[/TD]
[TD="width: 96, bgcolor: transparent"]Motor Vehicle
[/TD]
[TD="width: 64, bgcolor: transparent"]First Aid
[/TD]
[TD="width: 68, bgcolor: transparent"]Near Miss
[/TD]
[TD="width: 64, bgcolor: transparent"]Other
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]First Aid
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Yes
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Injury
[/TD]
[TD="bgcolor: transparent"]Yes
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Yes
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Property Damage
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Yes
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Other
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Yes
[/TD]
[/TR]
</tbody>[/TABLE]

Formula in B2
=IF(COUNTIF($C2:$I2,"Yes"),INDEX($C$1:$I$1,MATCH("Yes",$C2:$I2,0)),"")
copy down

M.

 

[Melo]

That worked perfectly. Thank you!!!

 

[Marcelo Branco]

That worked perfectly. Thank you!!!

You are welcome. Glad for helping


M.