Sumproduct - Closest to?

[jamescooper]

	E	F	G	H
1. Trapstyle Thea
1. Trapstyle Thea
1. Trapstyle Thea
1. Trapstyle Thea
1. Trapstyle Thea
1. Trapstyle Thea
1. Trapstyle Thea
1. Trapstyle Thea
1. Trapstyle Thea
1. Trapstyle Thea
1. Trapstyle Thea
1. Trapstyle Thea
1. Trapstyle Thea
1. Trapstyle Thea
1. Trapstyle Thea
1. Trapstyle Thea
1. Trapstyle Thea
1. Trapstyle Thea
1. Trapstyle Thea

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]36[/TD]

[TD="align: right"]96[/TD]
[TD="align: right"]14.50[/TD]
[TD="align: right"]£127.89[/TD]

[TD="align: center"]37[/TD]

[TD="align: right"]94[/TD]
[TD="align: right"]16.50[/TD]
[TD="align: right"]£134.52[/TD]

[TD="align: center"]38[/TD]

[TD="align: right"]91[/TD]
[TD="align: right"]16.00[/TD]
[TD="align: right"]£138.52[/TD]

[TD="align: center"]39[/TD]

[TD="align: right"]89[/TD]
[TD="align: right"]16.00[/TD]
[TD="align: right"]£142.52[/TD]

[TD="align: center"]40[/TD]

[TD="align: right"]85[/TD]
[TD="align: right"]16.00[/TD]
[TD="align: right"]£146.52[/TD]

[TD="align: center"]41[/TD]

[TD="align: right"]81[/TD]
[TD="align: right"]19.00[/TD]
[TD="align: right"]£151.30[/TD]

[TD="align: center"]42[/TD]

[TD="align: right"]80[/TD]
[TD="align: right"]16.50[/TD]
[TD="align: right"]£160.57[/TD]

[TD="align: center"]43[/TD]

[TD="align: right"]73[/TD]
[TD="align: right"]18.50[/TD]
[TD="align: right"]£161.57[/TD]

[TD="align: center"]44[/TD]

[TD="align: right"]70[/TD]
[TD="align: right"]18.50[/TD]
[TD="align: right"]£161.60[/TD]

[TD="align: center"]45[/TD]

[TD="align: right"]64[/TD]
[TD="align: right"]19.00[/TD]
[TD="align: right"]£170.31[/TD]

[TD="align: center"]46[/TD]

[TD="align: right"]60[/TD]
[TD="align: right"]15.50[/TD]
[TD="align: right"]£190.34[/TD]

[TD="align: center"]47[/TD]

[TD="align: right"]59[/TD]
[TD="align: right"]15.50[/TD]
[TD="align: right"]£195.54[/TD]

[TD="align: center"]48[/TD]

[TD="align: right"]51[/TD]
[TD="align: right"]16.00[/TD]
[TD="align: right"]£195.83[/TD]

[TD="align: center"]49[/TD]

[TD="align: right"]49[/TD]
[TD="align: right"]15.00[/TD]
[TD="align: right"]£217.83[/TD]

[TD="align: center"]50[/TD]

[TD="align: right"]49[/TD]
[TD="align: right"]16.00[/TD]
[TD="align: right"]£218.17[/TD]

[TD="align: center"]51[/TD]

[TD="align: right"]49[/TD]
[TD="align: right"]15.00[/TD]
[TD="align: right"]£218.25[/TD]

[TD="align: center"]52[/TD]

[TD="align: right"]45[/TD]
[TD="align: right"]16.00[/TD]
[TD="align: right"]£218.65[/TD]

[TD="align: center"]53[/TD]

[TD="align: right"]43[/TD]
[TD="align: right"]16.00[/TD]
[TD="align: right"]£223.85[/TD]

[TD="align: center"]54[/TD]

[TD="align: right"]43[/TD]
[TD="align: right"]16.00[/TD]
[TD="align: right"]£224.06[/TD]

</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.