Tell me how you can return a value that satisfies the following condition:

There is a number. It is necessary to find the range in which this number falls, returning the value corresponding to this range, and the range is selected from the condition. As follows:

ABC Диапазоны Условие Значение диапазона 1.12230 21 II 2.51764 21 II 3.59535 21 III 4.60691 21 III 5.80136 21 IV 6.95233 21 IV 7.121573 22 II 8.212050 22 II 9.235020 22 III 10.260778 22 III 11.331025 23 I 12.387000 23 I 13.400000 23 II 14.531143 23 II

For example, Number = 92333, condition = 21. Analyzing the list of ranges with condition 21, we find that this number is satisfied by a range of 5-6, which means that IV should be displayed

UPD

  ABC Диапазоны Условие Значение диапазона 1.12230 21 II 2.51764 21 II 3.51765 21 III 4.60691 21 III

It turns out the array should be processed, not the first incoming number. Ie drive first through a range of 1-2, then 3-4, etc.

  • Is it possible that the condition does not fit the appropriate range (example: Number = 92333, condition = 22)? Or the option when different numbers in column 3 (example: ranges 5, 6, but in C they have different values). If so, how to be in this case? - vikttur
  • @vikttur is possible, you can specify "no data" - GRM
  • @vikttur With your second example of this is impossible, because 1-2 3-4 and so on are ranges, if you can express the segments [], i.e. for all extreme results of the range will be the same values ​​as for their contents. - GRM

1 answer 1

The value of column C for the lower limit of the range:

 =ЕСЛИ(ИНДЕКС(B2:B21;ПОИСКПОЗ(D1;A2:A21))=E1;ИНДЕКС(C2:C21;ПОИСКПОЗ(D1;A2:A21));"нет")

The value of the column C for the upper limit of the range:

 =ЕСЛИ(ИНДЕКС(B2:B21;ПОИСКПОЗ(D1;A2:A21)+1-СЧЁТЕСЛИ(A2:A21;D1))=E1;ИНДЕКС(C2:C21;ПОИСКПОЗ(D1;A2:A21)+1-СЧЁТЕСЛИ(A2:A21;D1));"нет")

where D1 is a number, E1 is a condition

It remains to check the obtained values ​​for equality.

enter image description here

 -------------------------------

The formulas shown above work with column A values ​​in ascending order.

For unsorted data, the formula is more complicated:

 =МАКС(ЕСЛИ(A2:A20<=D1;ЕСЛИ(A3:A21>=D1;ЕСЛИ(B2:B20=E1;ЕСЛИ(B3:B21=E1;ЕСЛИ(C2:C20=C3:C21;СТРОКА(A2:A20)))))))

The formula is entered as an array formula ( Ctrl + Shift + Enter ). Specifies the desired string. If there is no match, it will show zero.

It was possible to write shorter: instead of a cascade IF apply a logical AND:

 (A2:A20<=D1)*(A3:A21>=D1)*(B2:B20=E1)*(B3:B21=E1)*(C2:C20=C3)

but in this case, the formula will take a lot more, and IF you can cut off extra calculations.

The result is used to look up the value in column C:

 =ЕСЛИ(D2;ИНДЕКС(C2:C20;D2-1);"нет")

where D2 is a string definition formula.

  • applied this answer, but because put the question wrong. cm UPD - GRM
  • Fooms will work with the last change in the conditions of the problem. Or I did not understand what was needed. - vikttur
  • I put it poorly, if taking into account the update, take the number 51800, then at the output I will get the value II for the lower range and for the upper one, but for III. Because The second line of processing goes earlier than the 3rd, then it probably turns out II? - GRM
  • See the picture added to the answer. Result - III. If you say that 51800 does not fall into any of the ranges (example before UPD), then here too - the order - two formulas will show different values: II and III. And the comparison of these values ​​will show that the number fell out of bounds. - vikttur
  • ! Clickable In the green range is a number, but the value is from 51746 . - GRM