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

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

Table of Contents

**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).

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.

**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.

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).

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.

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.

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 1^{st} option i.e. (…)TRUE – Approximate match.

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.

**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:

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

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

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

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.

**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))**

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.

There you go.

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