# 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.

+3

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

+2

source

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

and fill in.

In the F1: `=max(E1:E5)`

. This is your desired result.

0

source

All Articles