# 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