When you are dealing with thousands of data entries, you prefer to have a systematic order😬 in the same. This can be done in one simple way, adding things into groups. This will make the data look more organised📚.
To understand the same, let’s take an example:
As you can see in the image above, we have product id along with the sales of it. As you can observe in the same, the sales range is present in a very haphazard manner, so to bring it into an organised form, we can use the help of VLOOKUP Function to arrange the data into groups.
Previously: Find Next largest match with MATCH Formula
The first thing that we will do is input the VLOOKUP Function i.e. as follows:
The following function requires lookup value, table array, column index number, and range lookup.
*Please note: when you are trying to put your data into groups, then you should create a format accordingly, by this we mean that you should give an example of the same for whether how or which number would you consider in what group (refer to the right side sales and group data in the image above).
Coming back to the formula, the first thing comes is the lookup value. Now, as we want the sales data to be in a group form, so we will select the same, i.e. cell C2.
Then we will select the table array, i.e. the type of format, or the type of groups we want to form or in simple words, the customization we want to make to our data.
So, in the example above, our table array will be the table given on the right-hand side (RHS) i.e. I4:K9.
Then we come to the point of column index number🤔. The column index number is the column number from the table array which is our reference. So in this case it is the “Group” which is 2nd column number in the table array.
Then comes the question of range lookup, in this it will be asked for whether the said data which you have entered in the formula is true or false. Here, true means approximate match and false means exact match.
As we have uneven intervals, and the data that we have shown in the table array is a mere example, so we will go with option of TRUE i.e. Approximate match🤓.
After choosing the right option in range lookup, we will close the VLOOKUP Function, the whole formula is as follows:
Now all we have to do is press enter, and our work will be done. As you can observe in the image above, the data we were holding is converted into a group form, the sales value i.e. 23000 comes under the group of 21000-30000.
Now, all you have to do is drag the formula down so that it saves your time.
It’s easy, isn’t it😎?