Find closest match in a horizontal

[edtwiest]

I have items with a current value and now we have done some calculations en the new results are columns E-L

If i want to have the nearest match to item 1-002, the nearest match should be 150 [closer to 167,39 then 207]

I've tried to do index and match with multiple criteria, but all i got was errors.
Maybe some kind of matrix-formule??

I hope some wizzkid overhere is able to help me out.

Thanks in advance, Edwin

 

[Peter_SSs]

Two things would help:

• Update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
• Investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

 

[edtwiest]

horizontal lookup closest match.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	itemno	current value	W-value		itemno	w1	w2	w3	w4	w5	w6	w7
2	1-002	167,39			1-002	95	73	91	92	207	150	305
3	12-001	16,53			13-002	24	4	184	138	67	113	85
4	2-305	240,76			1-003	19	157	47	76	64	11	113
5	4-225	452,59			1-010	12	3	44	69	235	46	41
6	1-010	103,33			12-001	142	140	144	6	10	75	51
7	1-003	23,77			2-002	403	161	240	263	152	207	188
8	2-002	290,36			2-305	314	330	137	151	155	95	228
9	13-002	37,2			4-225	375	590	559	518	528	369	550
Blad1

 

[Peter_SSs]

Thanks for addressing both issues.

Not sure why you marked 51 in green when 10 is much closer?
Try this.

22 03 01.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	itemno	current value	W-value		itemno	w1	w2	w3	w4	w5	w6	w7
2	1-002	167.39	150		1-002	95	73	91	92	207	150	305
3	12-001	16.53	10		13-002	24	4	184	138	67	113	85
4	2-305	240.76	228		1-003	19	157	47	76	64	11	113
5	4-225	452.59	518		1-010	12	3	44	69	235	46	41
6	1-010	103.33	69		12-001	142	140	144	6	10	75	51
7	1-003	23.77	19		2-002	403	161	240	263	152	207	188
8	2-002	290.36	263		2-305	314	330	137	151	155	95	228
9	13-002	37.2	24		4-225	375	590	559	518	528	369	550
Closest

Cell Formulas
Range	Formula
C2:C9	C2	=INDEX(F$2:L$9,MATCH(A2,E$2:E$9,0),MATCH(MIN(ABS(INDEX(F$2:L$9,MATCH(A2,E$2:E$9,0),0)-B2)),ABS(INDEX(F$2:L$9,MATCH(A2,E$2:E$9,0),0)-B2),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jorismoerings]

Hi,

same answer, same question, other possible solution

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	itemno	current value	W-value		itemno	w1	w2	w3	w4	w5	w6	w7
2	1-002	167,39	150		1-002	95	73	91	92	207	150	305
3	12-001	16,53	10		13-002	24	4	184	138	67	113	85
4	2-305	240,76	228		1-003	19	157	47	76	64	11	113
5	4-225	452,59	518		1-010	12	3	44	69	235	46	41
6	1-010	103,33	69		12-001	142	140	144	6	10	75	51
7	1-003	23,77	19		2-002	403	161	240	263	152	207	188
8	2-002	290,36	263		2-305	314	330	137	151	155	95	228
9	13-002	37,2	24		4-225	375	590	559	518	528	369	550
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=LET(i,INDEX($F$2:$L$9,MATCH($A2,$E$2:$E$9,0),0),j,ABS(i-B2),INDEX(i,MATCH(MIN(j),ABS(j),0)))

 

[Peter_SSs]

same answer, same question, other possible solution

.. except that the OP does not have the LET function since using Excel 2016.

 

[edtwiest]

.. except that the OP does not have the LET function since using Excel 2016.

Always useful to have an O365 solution aswell! Thanks!

 

[Peter_SSs]

Always useful to have an O365 solution aswell!

In that case I would make the further saving of using FILTER instead of (one) INDEX/MATCH
(I have assumed values in column E do not repeat)

22 03 01.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	itemno	current value	W-value		itemno	w1	w2	w3	w4	w5	w6	w7
2	1-002	167.39	150		1-002	95	73	91	92	207	150	305
3	12-001	16.53	10		13-002	24	4	184	138	67	113	85
4	2-305	240.76	228		1-003	19	157	47	76	64	11	113
5	4-225	452.59	518		1-010	12	3	44	69	235	46	41
6	1-010	103.33	69		12-001	142	140	144	6	10	75	51
7	1-003	23.77	19		2-002	403	161	240	263	152	207	188
8	2-002	290.36	263		2-305	314	330	137	151	155	95	228
9	13-002	37.2	24		4-225	375	590	559	518	528	369	550
Closest (2)

Cell Formulas
Range	Formula
C2:C9	C2	=LET(f,FILTER(F$2:L$9,E$2:E$9=A2),a,ABS(f-B2),INDEX(f,MATCH(MIN(a),a,0)))

 

[Peter_SSs]

@edtwiest
I was just wondering about an example like this. What result(s) would you want - assuming such data was possible?

22 03 01.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	itemno	current value	W-value		itemno	w1	w2	w3	w4	w5	w6	w7
2	1-002	200			1-002	95	210	91	92	220	190	305
Closest (3)

 

[edtwiest]

Hi,

same answer, same question, other possible solution

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	itemno	current value	W-value		itemno	w1	w2	w3	w4	w5	w6	w7
2	1-002	167,39	150		1-002	95	73	91	92	207	150	305
3	12-001	16,53	10		13-002	24	4	184	138	67	113	85
4	2-305	240,76	228		1-003	19	157	47	76	64	11	113
5	4-225	452,59	518		1-010	12	3	44	69	235	46	41
6	1-010	103,33	69		12-001	142	140	144	6	10	75	51
7	1-003	23,77	19		2-002	403	161	240	263	152	207	188
8	2-002	290,36	263		2-305	314	330	137	151	155	95	228
9	13-002	37,2	24		4-225	375	590	559	518	528	369	550
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=LET(i,INDEX($F$2:$L$9,MATCH($A2,$E$2:$E$9,0),0),j,ABS(i-B2),INDEX(i,MATCH(MIN(j),ABS(j),0)))

@edtwiest
I was just wondering about an example like this. What result(s) would you want - assuming such data was possible?

22 03 01.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	itemno	current value	W-value		itemno	w1	w2	w3	w4	w5	w6	w7
2	1-002	200			1-002	95	210	91	92	220	190	305
Closest (3)

Hi Peter, in that case i'd like to have 210 as a result, but mostly your formule (2016) should be fine.... unless there is an easy way for you to adjust that first formula...