How to bucket values with LOOKUP function in Microsoft Excel

Lets look at methods to bucket or bin a set of values. Imagine you have the data in the following format.

There are two methods to achieve this:

Lets say we have to bucket the above values into 0-100, 100-500 and greater than 500 categories.

1. Using Nested If function: We have to write several nested IFs from lower value category to higher to categorize. Which I feel takes longer time, complex and error-prone. The length of the formulae is huge as well.

Imagining your value in cell B2 here is the nested IF formula:

=IF(AND(B2>=0,B2<100),"0-100",IF(AND(B2>=100,B2<500),"100-500",">500"))

2. Using LOOKUP function: In this method, the LOOKUP function is used in the following manner to assign categories. The formulae is shorter and neatly organized. Scalability is better in this method than nested IF. 

Imagining your value in cell B2 here is the LOOKUP formula:

=LOOKUP(B2,{0,100,500},{"0-100","100-500",">500"})

LOOKUP formula explained in detail:

Components of LOOKUP formula:

• LOOKUP_VALUE: Cell reference of the value which needs to be categorized.
• LOOKUP_VECTOR: Cut off values for each category. In the above case we are defining the values at 100,500 intervals. Hence the lookup vector would be {0,100,500} in our example.
• RESULT_VECTOR: Category names to be assigned to the intervals we entered in the previous point. In the above example it is {"0-100","100-500",">500"}

Thanks for visiting. If you have any questions reach out to me directly at askme.kkhelps@gmail.com