How to assign values from intervals

The following table presents a list of customers. They are collecting the points.

Customers list

Membership depends on the number of points.  The task is to assign correct membership from points intervals.

Points intervals


When you have a multiple conditions to be evaluated, it is not always necessary to use the IF function. In this case, it is best to use the search function VLOOKUP. Let’s see how it works.

Step 1 / create auxiliary table

The auxiliary table will contains the beginning points of intervals. Intervals have to be sorted in ascending order. This table can be placed anywhere.

Auxiliary table


Step 2 / use VLOOKUP

VLOOKUP function has 2 variations in finding values. It is to find an exact match and to find the closest match. This behavior affects the last argument Range_Lookup.

Select cell C2 and write this formula:


or fill VLOOKUP arguments window:

VLOOKUP arguments


Lookup_value is cell B2 (value 154). If this value is not in the first column of Table_array, then will VLOOKUP search for the closest lower value. So, it finds value 101 and result is gold member.

$ character in the second argument is important, because when you copy this formula, Table_array argument must be still the same. VLOOKUP would not work correctly without the $ character in the second argument.

Col_index_num is 2, because Membership column is on the second place in the auxiliary table (in the Table_array).

Range_lookup is 1, because we want to find the closest match. Here you can also write the word TRUE.

Now copy formula from C2 to other cells.

Final breakdown into intervals


Add a comment »One comment to this article

  1. WOW

    it’s very simple. I always wonder how is Excel powerful when you are smart and able to use Excel in the proper way.

    Btw. Alex Excel :) nice nickname


Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress