Index Match MATCH Closest Value

[Lewzerrrr]

Hi,

I know how to perform a CSE of the usual Index Match to nearest value which just looks at 1 column.

=INDEX(A7:A16,MATCH(TRUE,ABS(B7:B16-B2)=MIN(ABS(B7:B16-B2)),0))

How can I take this further to add the column index match?

For example, the data below isn't taking the name into account and purely just looking at the nearest value of 150 in column B.

I need it to look for the name aswell, is this possible?

John 150 returns Bandwith 9.
Karen 300 returns Bandwith 3.

Name:	John
Value:	150
Returns:	Bandwith 9
	John	Dave	Jim	Karen	Jane
Bandwith 1	482	474	450	389	358
Bandwith 2	433	376	447	389	354
Bandwith 3	369	329	408	313	346
Bandwith 4	336	222	244	249	314
Bandwith 5	329	165	239	182	216
Bandwith 6	324	162	217	133	180
Bandwith 7	321	154	176	131	119
Bandwith 8	245	73	47	92	91
Bandwith 9	176	65	8	76	38
Bandwith 10	56	14	5	67	32

<tbody>
</tbody>

 

[Domenic]

Try...

=INDEX(A7:A16,MATCH(TRUE,ABS(INDEX(B7:F16,0,MATCH(B1,B6:F6,0))-B2)=MIN(ABS(INDEX(B7:F16,0,MATCH(B1,B6:F6,0))-B2)),0))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Oscar Cronquist]

	A​	B​	C​	D​	E​	F​
1​	Name:	Karen
2​	Value:	300​
3​	Returns:	Bandwith 3
4​		John	Dave	Jim	Karen	Jane
5​	Bandwith 1	482​	474​	450​	389​	358​
6​	Bandwith 2	433​	376​	447​	389​	354​
7​	Bandwith 3	369​	329​	408​	313​	346​
8​	Bandwith 4	336​	222​	244​	249​	314​
9​	Bandwith 5	329​	165​	239​	182​	216​
10​	Bandwith 6	324​	162​	217​	133​	180​
11​	Bandwith 7	321​	154​	176​	131​	119​
12​	Bandwith 8	245​	73​	47​	92​	91​
13​	Bandwith 9	176​	65​	8​	76​	38​
14​	Bandwith 10	56​	14​	5​	67​	32​

Sheet: Sheet54

Formula in cell B3:
=INDEX(A5:A14,MATCH(TRUE,ABS(INDEX(B5:F14,0,MATCH(B1,B4:F4,0))-B2)=MIN(ABS(INDEX(B5:F14,0,MATCH(B1,B4:F4,0))-B2)),0))

 

[Lewzerrrr]

Thanks both!

If I wanted to offset by 1 column how would I do that?

For example, the names appear as a header and theres 2 subheaders below each.

I can use that formula to get the first sub header but how can I adjust to look down the second subheader?

SOLVED.

=INDEX(A5:A14,MATCH(TRUE,ABS(INDEX(B5:G14,0,MATCH(B1,B4:G4,0)+1)-B2)=MIN(ABS(INDEX(B5:G14,0,MATCH(B1,B4:G4,0)+1)-B2)),0))