How to assign values from intervals
The following table presents a list of customers. They are collecting the points.
Membership depends on the number of points. The task is to assign correct membership from 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.
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:
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.