When you are dealing with the sales💰 of the products and the upper management asks🤔 for the details of the product which has gained the least sales in the particular month, then to find out the same, this is what you can do:
As you can see in the example above, we have a bunch of product IDs and the sales of the same🤷.
Now to find out the product which has made the lowest sales value we will take the help of INDEX Function, MATCH Function and MIN Function.
So, let’s get started:
[toc]
Step 1: Input the formula =INDEX(
The first thing that we need to do is input the INDEX Function which is as follows:
=INDEX(
Step 2: Input the required values in the formula
The following formula requires array or reference, row number, column number (optional at times) and area number (optional as well).
As the first thing which is required in the formula is the array/ reference, so we will select the Product ID range. This is because, we are not just looking for the lowest sales value but instead we are looking for the product ID which has made the lowest sales.
So, in the above example, the array/ reference becomes B3:B11 (Product ID range).
Step 3: Input the formula =MATCH(
Then we will input the MATCH Function which is as follows:
=MATCH(
Step 4: Input the required values in the formula
The following formula requires a lookup value, lookup array, and match type (which can be optional at times).
So, in the lookup value, we will input the MIN Function, this is being done because we are looking for the least sales value, if you want to find the highest, you can replace the MIN Function from the MAX Function.
Step 5: Input the formula =MIN(
The MIN Function is as follows:
=MIN(
Step 6: Input the required values in the formula
The following formula requires number 1, number 2, and so on…
As the requirement of the MIN Function is numbers, we need to select all the sales values that we have or from which we need to find out the least sales value. So, as you can see in the image above, we have selected the Sales range, i.e. C3:C11.
After the selection of sales range, we will close the MIN Function and move back to the MATCH Function. Now, in the same, the next we need to look for is the lookup array, this will be the sales range only as we are finding the least sales value from the sales range.
So, the lookup array is C3:C11.
After inputting the lookup array, we move to the match type. As you can see in the image above, there are three options:
(…)1 – Less than
(…)2 – Exact match
(…)3 – Greater than
As all the values that we have inputted till now are exactly the way we want, we will go with the option: (…)0 – Exact match.
Step 7: Cross- check the formula and proceed
Now all we have to do is close all the functions in hand. By this, our overall formula becomes as follows:
=INDEX(B3:B11,MATCH(MIN(C3:C11),C3:C11,0))
Now all we have to do is press enter and BAMMMMM!!! The answer is 346. This means that the product holding the ID number 346 is the one having the least sales of all.
So, it’s easy, isn’t it😉?
Leave a Reply