Multiple nested AND functions in an IF statement

**Cortlyn** · 03-05-2013, 05:06 PM

Hi
I would like to add a comment in B23 from C3:C18 that is the combination of the Objective and Competence values selected in the drop downs in B21 and B22. I was using “ IF(AND(A3="Exceeded",B3="Exceeded"),C3, etc.. to cover all combinations, but I am sure that there is a simpler way.
Thanks
Regards
Cortlyn

**JBeaucaire** · 03-05-2013, 05:15 PM

1) Change the value in F1 to Exceeded (these strings need to match exactly)

2) Fix the choices in B21 and B22 in case you want them the be Exceeded

3) The formula in B23 would be:

=INDEX($C$3:$C$18, MATCH(B21&B22, INDEX($A$3:$A$18&$B$3:$B$18, 0), 0))

**Cortlyn** · 03-05-2013, 05:25 PM

Thank you very much.

Regards

Cortlyn

**mehmetcik** · 03-05-2013, 05:41 PM

This concatanates all the strings in column B if that is what you need.

Please Login or Register  to view this content.

However I think that what you need is to count the appearances of "Exceeded" and if all a certain percentage then make one statement if not another.

What you would need for that is the Countif function.

I have played with your spreadsheet a bit.

I hope you like the changes.