Function to Find Closest Value without Going Under

[LindonB]

Hi There!

I'm trying to replicate something that I did for testing in Excel.

Basically, I have a large list of values. For the attached example, they are just....

{3,6,9,...,30}. 

These really represent ranges. In my other table (in the simplified example), I have values that are between 3 and 30 but are random, so...

{3,6,6,12,12,18,21,21,24,30}.

I want to return the closest value from the first table for each value in the second without going under.

In other words, the first table reads 3 but represents the range of values 3<=6. Therefore, when I have a random value of 3 (or 4...), it would simply return 3.

The function in Excel to do this would be an array:
={MIN(IF($A$2:$A$11>=$C2,$A$2:$A$11))}, where
$A$2:$A$11 = Value of Ranges (First Table)

and

$C2 the cell containing the value.

[fmvizcaino]

Hi @LindonB ,

To achieve that, I have used a batch macro to get the closest value one by one.

Let me know if that works for you.

Best,

Fernando Vizcaino

[WilliamR · Accepted solution]

Hello @LindonB , here is a solution with the comment included in the workflow.

(If this post helps, then please consider it as the solution to help the other members find it more quickly).

[LindonB]

Hi @WilliamR,

Thanks a million. I used a similar join approach. My only concern is generating so many unwanted rows, given the size of my data set. Still, that does seem to be the best approach! Thanks again.