# return Approximate match decimal number

#### elsg

##### Active Member
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

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### NeonRedSharpie

##### Well-known Member
Code:
``````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

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

#### NeonRedSharpie

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

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

but changing that to

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

didn't return what I expected.

#### NeonRedSharpie

##### Well-known Member
Code:
``=I1+MIN(ABS(I1-A1:D1))``

Here's a bit of a hack...

#### elsg

##### Active Member
Very nice, thank you!!

#### Weazel

##### Well-known Member
could try something like....

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

Replies
4
Views
463
Replies
7
Views
532
Replies
5
Views
234
Replies
1
Views
751
Replies
4
Views
811

1,195,661
Messages
6,010,978
Members
441,579
Latest member
satishrazdhan

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back