return Approximate match decimal number

[elsg]

How return Approximate match decimal number?
My matrix below (A1:D1)

38,50

38,60

39,00

38,40

<tbody>
</tbody>

I fill cell --> I1 = 38,47
Result cell --> I2 ​= 38,50

 

[NeonRedSharpie]

Function approx(val As Range, rng As Range) As Variant

    Dim achk As Double


    achk = 100000


    For Each cell In rng
    
        Select Case Abs(cell - val.Value)
        Case Is < achk
            achk = cell - val.Value
            approx = cell.Value
        End Select
    Next


End Function

Here's a custom function with cell ranges as inputs.

 

[elsg]

Good, but by curiosity, is there way solution by formula-Excel?

 

[NeonRedSharpie]

I tried using HLOOKUP and for some reason it didn't return the expected result. It will return exact matches with :

=HLOOKUP(A2,A1:D1,1,0)

but changing that to

=HLOOKUP(A2,A1:D1,1,1)

didn't return what I expected.

 

[NeonRedSharpie]

=I1+MIN(ABS(I1-A1:D1))

Here's a bit of a hack...

 

[elsg]

Very nice, thank you!!

 

[Weazel]

could try something like....

=INDEX(A1:D1,MATCH(I1,SMALL(A1:D1,{1,2,3,4})))