Sometimes I need to know if all conditions are reunited, and if it is true, I need to display a particular value. In the other hand, it may happen that 1 of those conditions is a cell containing a word or comment, so I need a way to match for instance the first 6 letters.

When I use the formula ?

When I need to match multiple conditions to be true.

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

How are the formulas ?

=IF()

=AND()

=LEFT()

=VLOOKUP()

This formula is to match 2 conditions:

=IF(AND(A2="chocolate",B2="dark"),"yes","no")

So if in A2 is chocolate and in B2 is dark, it will display “yes”, if not “no”.

This formula is the same thing as above except that I am including a 3rd conditions with words:

=IF(AND(A2="chocolate",B2="dark",LEFT(C2,6)="SP COO"),"yes","no")

So the only difference is in C2, I am asking the first 6 characters starting on the left. To test the word matching, you can use this formula:

=IF(LEFT(C2,6)="SP COO","yes","no")

So if in C2, the first 6 letters on the left match “sp coo”, it will display “yes”, if not “no”.

I can do the same thing with vlookup:

=IF(LEFT(VLOOKUP(C2,C:C,1,0),3)="sp ","chocolate","no chocolate")

In this one, I am asking to search in the cell C2 the first 3 letters including the space, if it finds, it will display chocolate.