When you have a proper detailed or systematic requirement regarding some data, this how you can categorize the same🤓🤔:

override output

The example above will give you a more detailed note regarding what we are talking about…

Step 1: Input the formula =IF(

As you can see in the image above, there is systematic order provided in the left hand side (LHS) of the sheet. This order is required to be followed on the data in hand which is shown on the right hand side (RHS).

override output

To do the same, we will be using the IF Function as well as the VLOOKUP Function.

So, let’s begin by inputting the IF Function which is as follows:

=IF(

Step 2: Input the required values in the formula

The following formula requires a logical test which will be the VLOOKUP Function, value if true and value if false.

override output

Step 3: Input the formula =VLOOKUP(

Now as we have talked above, that the logical test in the IF Function will be the VLOOKUP Function, so we will input the same, which is as follows:

=VLOOKUP(

Step 4: Input the required values in the formula

The following formula requires lookup value, table array, column index number, and range lookup which can be optional at times.

override output

So, as we need to systematize the sales of products into lots, the product’s sale will be our lookup value. As per the above example, the lookup value is cell F3, i.e. 30000 (sales of product ID 234).

override output

Then we will come to the aspect of table array which is basically the system or the order that we need to follow, which is provided on the LHS (Left Hand Side) of the sheet i.e. B3:C8.

override output

Then we need to jot down the column index number, as we want the sales to be categorized into lots, so the column index number becomes 2, this is so, as the Lots column is second in number, in the table array which we selected before.

override output

Now, comes the range lookup, in the same, there will be two options which will be as follows:

(…)TRUE – Approximate match

(…)FALSE – Exact match

As the LHS table has shown an approximation of sales which are later categorized into lots, so we will be using the 1st option i.e. (…)TRUE – Approximate match.

override output

After inputting the range lookup, we will close the VLOOKUP Function and come back to the IF Function. Here, we are asked about the value if true and value if false. These two are required for the sake, for if the sales value does not fit in, in any of the lots mentioned in the table array, then the value if false could be used.

This is the reason why we have written =“0” this as value if true and “1” this as value if false. It’s your call, if you want to write something else.

*Please note: Do not forget the equal to sign right before the value if true.

override output

Step 5: Input the formula =VLOOKUP(

After inputting the value if false, we will input another VLOOKUP Function, continuing in the IF Function only.

=VLOOKUP(

Step 6: Input the required values in the formula

As we know that the VLOOKUP Function requires lookup value, table array, column index number and range lookup, so this is what we will input in the same:

override output

All the steps will be repeated again, the lookup value will be the sales, so cell F3 i.e. 30000 of Product ID 234.

override output

Then the table array which is on the left hand side of the sheet, i.e. B3:C8.

override output

The column index number will be 2, as we need to categorize the sales of the products into lots.

override output

And last but not the least, the range lookup, which as we have talked above has two options in hand:

(…)TRUE – Approximate match

(…)FALSE – Exact match

We will be choosing the option (…)TRUE – Approximate match as the table array given on the left hand side is given on a approximation only.

override output

Step 7: Cross- check the formula and proceed

After closing the VLOOKUP Function and then the IF Function, the overall formula in hand becomes as follows:

=IF(VLOOKUP(F3,B3:C8,2,TRUE)=”0”,”1”,VLOOKUP(F3,B3:C8,2,TRUE))

override output

Now all you have to do is press enter and drag down the formula for each sales value to categorize the same into Lots as per the table array given on LHS of the sheet.

override output

There you go.

override output

So, it’s easy, isn’t it😉?