It is given.

Table with groups of distances:

  1. | <300 km
  2. | 301-1000 km
  3. | 1001-2000 km.
  4. | 2001–4000 km.
  5. etc.

And there is a distance value between cities equal to 1139 .

Question.

How, using just one cell of the Excel table, display in it the value of the group to which the specified distance belongs (ie, 1,2,3,4, etc.)? Without the use of the IF and OR functions, this is too easy.

    1 answer 1

    In one cell of the table (column B), place not the range of distances, but the beginning of the range.

    In this case, you can apply an inaccurate search in MATCH :

    =ИНДЕКС(A1:A50;ПОИСКПОЗ(1139;B1:B50;1))

    If the columns are swapped (in A - the beginning of the ranges, B - groups) - inaccurate search using CDF :

     =ВПР(1139;A1:B50;2)

    Instead of the desired value in the formula, you can enter a reference to the cell with this value.

    Important: the values ​​of the beginning of the ranges must be arranged in ascending order, the first value is 0 (the beginning of the first range).

    • There is nothing more to add. Works. Thank. - Artem Dozorov