When you want to find a particular data, which has some sort of specifications but you have loads and loads of data in your hand so it becomes kind of difficult, you have come to the right place.
When we are dealing with loads of entries and we need to find a specific data entry, we can find the same through the help of IF Function and OR Function.
Previously: How to use RANK Formula?
Let’s take an example for the same:
As you can observe in the image above, we have a product, colour, sales as well as a column for approval. Now, let’s say that only those products are approved which are either peach or blue in colour, so to do the same, or collect data over the same, here’s what you can do:
The first thing to be done is to input the IF Function:
The following formula requires logical test, value if true, value if false.
In the logical test, we will input the OR Function:
The following formula requires logical 1, logical 2, and so on…
In the first logic, as our whole approved or not approved basis is based on the colour, so we will select the colour of the product, i.e. cell C3 and we will put an equal to sign because we have a condition that the following colour can be either peach or blue only.
One thing you need to take care of is, you need to write you specifications separately, also you need to state them in quotation marks.
So, it becomes C3=”peach”
Another thing that you need to take care of is that you need to write the specification in the exact way it is present in your data, so if it’s written peach, you need to write peach only. You cannot write it as: Peach or PEACH. You need to write it in the exact format the way it is present in your data.
Moving on, we come to the aspect of another specification i.e. the blue colour. So, in the second logic we will again select the colour dell (C3) put an equal to sign in front and jot down blue in quotation marks.
Just like in the image above, C3=”blue”
Then we will close the OR Function as our work is done with it and we will continue working with the IF Function.
After going through the logical test, comes the value if true, so if say, a following product has the specifications that you are required, what is the word or character that you want the screen to show to you?
As you can observe in the image above, we have taken “Available” as the value if true.
And coming to the value if false, we have taken Not available.
One thing that you need to observe here is that be it the value if true or be it the value if false, we have jotted both down in the quotation marks as this is some sort of specifications that we are inputting in the formula, so excel requires quotation marks for the same, if you do not use them, then your formula will not work out.
Now all you have to do is close the IF Function, by this our whole formula with the combination of IF and OR Function becomes as follows:
Now shoot enter, and drag down the formula till it’s required.
Your work is finally done, from the image above you can see for which product no. is available in peach or blue colour and which one is not.
So, it’s easy, isn’t it?