An impossible Index match closest nth value multiple criteria

[stevenhill__]

Hi - Struggling for a solution having looked through a ton of threads and websites on what I would've imagined wouldn't be too challenging.

I want to be able to create a benchmarking tool whereby if I were to forecast sales for Site 1, it would show me the closest sales week, the labour cost from that week, the hours from the week and what week it was. Then be able to show the nth closest etc etc

I have a data source as per below:

I have used a standard Index Match abs formula which can get me the closest value of the entire data range but I want to just get the closest from each Site.
I also want to be able to send the Nth closest range.

If i can get the sales value right i can just index match the other numbers from there assuming there are no duplicates.

SOS.

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2	Site 1	a1	b1	c1	630			Site 1	500		442	c6	b6	a6
3	Site 2	a2	b2	c2	347
4	Site 3	a3	b3	c3	275
5	Site 4	a4	b4	c4	668
6	Site 5	a5	b5	c5	477
7	Site 1	a6	b6	c6	442
8	Site 2	a7	b7	c7	262
9	Site 3	a8	b8	c8	157
10	Site 4	a9	b9	c9	742
11	Site 5	a10	b10	c10	430
12	Site 1	a11	b11	c11	661
13	Site 2	a12	b12	c12	711
14	Site 3	a13	b13	c13	255
15	Site 4	a14	b14	c14	535
16	Site 5	a15	b15	c15	153
17	Site 1	a16	b16	c16	224
18	Site 2	a17	b17	c17	254
19	Site 3	a18	b18	c18	531
20	Site 4	a19	b19	c19	511
21	Site 5	a20	b20	c20	858
Main

Cell Formulas
Range	Formula
K2:N2	K2	=LET(f,FILTER(A2:E21,A2:A21=H2),INDEX(f,MATCH(MIN(ABS(INDEX(f,,5)-I2)),ABS(INDEX(f,,5)-I2),0),{5,4,3,2}))
Dynamic array formulas.