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
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
=LOOKUP(D1,C$1:C$5,A$1:A$5)
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
source to share