In a complex condition in Excel, there is a formula =ВПР(A2;{0;1:90;2:180;3:270;4};2;1) . But it does not work for me and I cannot understand it on the basis of the representation and description of the function. Can you help figure it out?

  • one
    "Does not work" does it give an error? Gives the wrong result? What exactly? In addition to what you yourself know about the work of the CDF function, what is its minimum view, what should you register? - AK ♦

1 answer 1

About CDF should be clear:

  =ВПР(что_ищем;где_ищем;здесь_нужное;ИСТИНА)

We are looking for the value of what's_chischem in the range where_ischische , when we find it, we show the value of the desired column here_number . The last parameter indicates how to search for a value — by exact ( FALSE, 0 ) or inaccurate ( TRUE, 1 ) coincidence.

In Excel, you can specify a range of constants. For this, a set of constant values ​​is enclosed in curly brackets, the separator between them can be ":" (line separator) or ";" (column delimiter).

 =ВПР(A2;{0;1:90;2:180;3:270;4};2;1)

Let's read the formula: find an inaccurate match (the last parameter is 1) of the value of cell A2 in the range of constants and show the value of the second column. The visual range of the constants of this formula can be represented in the cells of the sheet:

range

=========================== Examples.

A2 = 90

We are looking for 90. Found in the second row, looked at the second column - we have the result 2.

A2 = 92

We are looking for 92, in the second line 90 ... we go further ... 180. But this is> 92. We return to the line below, look at the second column - we have the result 2.

If there is an inaccurate coincidence, we find a value equal to the desired one or the largest of the smaller ones. If you specify an exact search, the formula when searching for the number 92 will show # N / A - there is no such thing in the range.

A2 = 500

The maximum value in the range of 270, it is also the largest among the smaller ones. In this case, the result is 4

  • Thank you very much - you helped me a lot. There was not enough data on constants! - Evgeny Anatolyevich
  • @YevgenyAnatolievich Mark the answer as accepted (green check mark to the left of the answer), since it helped you. - Vadim Ovchinnikov