Sumproduct - Closest to?

[jamescooper]

	E	F	G	H
36	1. Trapstyle Thea	96	14.50	£127.89
37	1. Trapstyle Thea	94	16.50	£134.52
38	1. Trapstyle Thea	91	16.00	£138.52
39	1. Trapstyle Thea	89	16.00	£142.52
40	1. Trapstyle Thea	85	16.00	£146.52
41	1. Trapstyle Thea	81	19.00	£151.30
42	1. Trapstyle Thea	80	16.50	£160.57
43	1. Trapstyle Thea	73	18.50	£161.57
44	1. Trapstyle Thea	70	18.50	£161.60
45	1. Trapstyle Thea	64	19.00	£170.31
46	1. Trapstyle Thea	60	15.50	£190.34
47	1. Trapstyle Thea	59	15.50	£195.54
48	1. Trapstyle Thea	51	16.00	£195.83
49	1. Trapstyle Thea	49	15.00	£217.83
50	1. Trapstyle Thea	49	16.00	£218.17
51	1. Trapstyle Thea	49	15.00	£218.25
52	1. Trapstyle Thea	45	16.00	£218.65
53	1. Trapstyle Thea	43	16.00	£223.85
54	1. Trapstyle Thea	43	16.00	£224.06

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Data​

I'd like to return the value in column H that is closest to 75 in column F and equal to 1. Trapstyle Thea.

I am thinking sumproduct?

Thanks.

 

[mikerickson]

"closest to 75 in column F and equal to 1"

What do you want to equal 1?
What result do you want from the sample data?

 

[jamescooper]

Sorry closest to 75 in column F and equal to in column E "1. Trapstyle Thea".

In this example it would be £161.57

Thanks.

 

[jamescooper]

Any views thanks.

 

[AlanY]

it will be easier with a helper column

Book1
	F	G	H	I	J	K	L
36	1. Trapstyle Thea	96	14.5	£127.89	21		1. Trapstyle Thea
37	1. Trapstyle Thea	94	16.5	£134.52	19		75
38	1. Trapstyle Thea	91	16	£138.52	16		161.57
39	1. Trapstyle Thea	89	16	£142.52	14
40	1. Trapstyle Thea	85	16	£146.52	10
41	1. Trapstyle Thea	81	19	£151.30	6
42	1. Trapstyle Thea	80	16.5	£160.57	5
43	1. Trapstyle Thea	73	18.5	£161.57	2
44	1. Trapstyle Thea	70	18.5	£161.60	5
45	1. Trapstyle Thea	64	19	£170.31	11
46	1. Trapstyle Thea	60	15.5	£190.34	15
47	1. Trapstyle Thea	59	15.5	£195.54	16
48	1. Trapstyle Thea	51	16	£195.83	24
49	1. Trapstyle Thea	49	15	£217.83	26
50	1. Trapstyle Thea	49	16	£218.17	26
51	1. Trapstyle Thea	49	15	£218.25	26
52	1. Trapstyle Thea	45	16	£218.65	30
53	1. Trapstyle Thea	43	16	£223.85	32
54	1. Trapstyle Thea	43	16	£224.06	32
Sheet1

Cell Formulas
Range	Formula
L38	=INDEX($I$36:$I$54,MATCH(MINIFS($J$36:$J$54,$F$36:$F$54,$L$36),$J$36:$J$54,0))
J36	=ABS(G36-$L$37)

 

[cunningAce]

Hi,

Try this array formula,

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	B	C	D	E	F	G	H	I
3	36	1. Trapstyle Thea	96	14.5	£127.89		Value	75
4	37	1. Trapstyle Thea	94	16.5	£134.52		Value	1. Trapstyle Thea
5	38	1. Trapstyle Thea	91	16	£138.52
6	39	1. Trapstyle Thea	89	16	£142.52		Result	161.57
7	40	1. Trapstyle Thea	85	16	£146.52
8	41	1. Trapstyle Thea	81	19	£151.30
9	42	1. Trapstyle Thea	80	16.5	£160.57
10	43	1. Trapstyle Thea	73	18.5	£161.57
11	44	1. Trapstyle Thea	70	18.5	£161.60
12	45	1. Trapstyle Thea	64	19	£170.31
13	46	1. Trapstyle Thea	60	15.5	£190.34
14	47	1. Trapstyle Thea	59	15.5	£195.54
15	48	1. Trapstyle Thea	51	16	£195.83
16	49	1. Trapstyle Thea	49	15	£217.83
17	50	1. Trapstyle Thea	49	16	£218.17
18	51	1. Trapstyle Thea	49	15	£218.25
19	52	1. Trapstyle Thea	45	16	£218.65
20	53	1. Trapstyle Thea	43	16	£223.85
21	54	1. Trapstyle Thea	43	16	£224.06
Sheet1

Cell Formulas
Range	Formula
I6	{=INDEX(F3:F21,MATCH(INDEX(D3:D21,MATCH(MIN(ABS(D3:D21-I3)),IF(C3:C21=I4,ABS(D3:D21-I3)),0)),D3:D21,0),1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

[/FONT]

 

[jamescooper]

Thanks, would this be possible without the helper column? Thanks.