Show a specific value when matching multiple data with or without characters with a formula in an excel report
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 is/are the formula(s) ?
=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.
Interesting Topics
-
Be successfully certified ITIL 4 Managing Professional
Study, study and study, I couldn’t be successfully certified without studying it, if you are interested...
-
Be successfully certified ITIL 4 Strategic Leader
With my ITIL 4 Managing Professional certification (ITIL MP) in the pocket, it was time to go for the...
-
Hide visual and change background color based on selection
Some small tricks to customize the background colour of a text box...
-
Stacked and clustered column chart or double stacked column chart
In excel, I use a lot the combination of clustered and stacked chart...