Find Closest Value to Specific Value in a Range.

[rmence]

I have something that looks like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0.05[/TD]
[TD]<<Target[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Data1[/TD]
[TD]Data2[/TD]
[TD]Data3[/TD]
[TD]Data4[/TD]
[TD]Data5[/TD]
[TD]Data6[/TD]
[TD]Closest[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.031[/TD]
[TD]0.0482[/TD]
[TD]0.0503[/TD]
[TD]0.0523[/TD]
[TD]0.091[/TD]
[TD]1.45[/TD]
[TD]0.0503[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



And I trying to figure out a formula that I could put in J3 that will give me the closest match to B1 from the range of D3:I3. The optimal value could be below or above the target, doesn't matter.

I hope that makes sense. I am not too versed in index match, but thinking that is probably the way to go, would sincerely appreciate any help.
Thankful for any direction someone smarter than me can provide.
-Rick

 

[Eric W]

Try this in J3:

=INDEX(D3:I3,MATCH(MIN(ABS(D3:I3-B1)),ABS(D3:I3-B1),0))

and confirm with Control+Shift+Enter.

 

[rmence]

That worked perfectly, thanks so much Eric!

 

[Eric W]

Glad to help!

 

[xtramiles]

Hi,

sorry I am new here and tried to find solution for my problem but a bit extension from that.
What if there is another data on row 4 and I want to find the closest value on J4 from range D3:I4. Tried with the same formula but the result is N/A.