Find the closest meaning without going over

I found similar questions, but they are at least slightly different from my question and have not been able to successfully adapt them, so here is a simplified version of my worksheet:

A     B     C     D     E
1     4     4     17
2     6     10
3     2     12
4     7     19
5     4     23


Column A is filled with 1-X integers. Column B is the number of occurrences of column A, and C is the sum of the values ​​to the left and above. D is a random value between 1 and C5. So far, so good. The problem is related to E1. I want it to give the A value that is to the left of the C value that is closest to D without going over.

Example: D corresponds to value 17. The closest value to 17 without transition is 12 (C3). So E is 3. How could I achieve this? I can get the closest value with =INDEX(A$1:A$5,MATCH(MIN(ABS(F1-C$1:C$5)),ABS(F1-C$1:C$5),0))

, but it comes up to 4 and not 3. How do I get the closest SMALL value? I am guessing that I need to replace the ABS SMALL, but I am not sure how.


source to share

2 answers

For this you can use the "vector form" LOOKUP

(see the help for the function LOOKUP

). If you search D1

in C1:C5

you will get exactly what you want (the largest value that is less than or equal to D1

) and then you can define the returned vector as A1:A5

to get the corresponding value from there


Note. You will get an error for values ​​D1 <4 because there is no "no jump" value in that case.

This C1:C5

needs to be sorted in ascending order to work , but this will always be the case in this scenario

Edit: I believe this answers your question as stated, but if you were looking for 17th place then there would be no result 4? If so, then I think you can still use LOOKUP

, but column C must be configured differently



In E1: =if(C1<=D$1,A1,"")

and fill in.

In the F1: =max(E1:E5)

. This is your desired result.



All Articles