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

Solution

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:

=VLOOKUP(B2,$E$2:$F$6,2,1)

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 »3 comments to this article

  1. Be careful, this doesn’t work properly!
    Ex: with 22 it will return the 21 result (bronze) instead of the 51 (silver), because it searches for the ‘nearest’ value.

    BR
    CC

    Reply

  2. Excel how to choose value from range. Thank you for this solution.

    Reply

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

    Reply



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