Function to Find Closest Value without Going Under

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.

Example.yxmd

Example.xlsx

Tips and Tricks

Fuzzy Match

Join

Accepted answers

WilliamR

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

Example.yxmd

All comments

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

2019-12-20 Function to Find Closest Value without Going Under.yxmd

2019-12-20 Function to Find Closest Value without Going Under.yxmc

WilliamR

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

Example.yxmd

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.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335