Finding Closest match in a range

[Jek61]

Hi Folks,

I am trying to return a value from a range of values closest to the input value (2.57 & 1.4 in the examples below).

I have a formula which works but has inconsistent results. The screenshot below shows the issue, when I check the formula section by section it should work.

As you can see below both sides in the index and match have the "same" resultant but the formula returns #N\A

Any assistance would be greatly appreciated.

Jek61

 

[Fluff]

Either use Xmatch, or set the match to exact match.

 

[Cubist]

Hi Fluff,

How would you do it with XMATCH? I don't believe the options within XMATCH return the "closest", only next smaller or larger. In my example below, even though 1 is smaller than the lookup val of 1.4 however, 1.5 is actually closer.

Book1
	D	E	F	G	H	I	J	K
8
9		Lookup Val	Result		Search array
10		1.4	1		1	1.5	2
11
12
Sheet4

Cell Formulas
Range	Formula
F10	F10	=XLOOKUP(E10,H10:J10,H10:J10,,-1)

 

[Jek61]

Thanks,

exact doesn't work and my version of excel does't have Xmatch, but thanks anyway.

Jek61

 

[Fluff]

my version of excel does't have Xmatch

Then what version are you using? Your profile has both 365 & 2016?

 

[Jek61]

Then what version are you using? Your profile has both 365 & 2016?

2016

Jek61

 

[Cubist]

Fluff's suggestion should work by setting match mode to 0.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2					2.5	1.8	1.3	0.9	0.8	0.6	0.55	0.275
3
4
5
6		1.4	1.3
7
8
Sheet4

Cell Formulas
Range	Formula
C6	C6	=INDEX(E2:L2, MATCH(MIN(ABS(E2:L2-B6)), ABS(E2:L2-B6), 0))

EDIT: You're hardcoding the 2.57 in the second formula.

 

[Jek61]

Thanks Fluff and Cubist now works perfectly.

I just struggled to understand why the match failed having a matching value.

Cheers

Jek61

 

[Fluff]

Glad we could help & thanks for the feedback.