Find Closest Value to Specific Value in a Range.

[rmence]

I have something that looks like this:

A	B	C	D	E	F	G	H	I	J
1	0.05	<<Target
2			Data1	Data2	Data3	Data4	Data5	Data6	Closest
3			0.031	0.0482	0.0503	0.0523	0.091	1.45	0.0503
4

<tbody>
</tbody>

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.