Weight Range Lookup

[Johnboy28]

Hi,

Can someone please help me with a couple of formulas?

1st Formula - "Code" If Weight Match "Weight Range" and "Client ID" from Left hand Table it will return Code?

2nd Formula - "Unit$" Match Code (Answer from Top Formula) and Date with Date Range and Code from bottom table to return unit$ Value?

I hope this makes sense. Thanks for your help.

Date	Client ID	Weight Range	Code		Date	Weight	Client ID	Code	Unit$
25/12/2012	Country	1	150	1		6/12/2012	362.60	Country	??	??
25/12/2012	Country	151	180	2		27/12/2013	225.00	Country
25/12/2012	Country	181	220	3		6/01/2013	349.00	South West
27/12/2012	Country	221	280	4		7/01/2013	363.30	Country
4/01/2013	Country	281	320	5		25/12/2012	183.40	Country
5/01/2013	Country	321	350	6		25/12/2012	201.30	Country
6/01/2013	Country	351	600	7		4/01/2013	320.80	Country
						5/01/2013	320.80	Country
4/01/2013	South West	1	150	8		6/01/2013	389.30	Country
4/01/2013	South	151	180	9		25/12/2012	200.40	Country
4/01/2013	South West	181	220	10		6/01/2013	389.30	City
4/01/2013	North	221	280	11		6/01/2013	401.30	Country
4/01/2013	South West	281	320	12		6/01/2013	380.00	City
5/01/2013	South West	321	350	13		4/01/2013	185.00	South
6/01/2013	City	351	600	14		4/01/2013	235.00	North
7/01/2013	City	321	350	15		4/01/2013	277.00	North
WC Date	Mon 24/12/12	Mon 31/12/12	Mon 07/01/13	Mon 14/01/13
WE Date	Sun 30/12/12	Sun 06/01/13	Sun 13/01/13	Sun 20/01/13
Code	Unit $	Unit $	Unit $	Unit $
1	$16.00	$16.00	$16.00	$16.00
2	$31.00	$31.00	$31.00	$31.00
3	$38.00	$38.00	$38.00	$38.00
4	$46.00	$46.00	$46.00	$46.00
5	$48.00	$48.00	$48.00	$48.00
6	$52.00	$52.00	$52.00	$52.00
7	$54.00	$54.00	$54.00	$54.00
8	$14.00	$14.00	$14.00	$14.00
9	$28.00	$28.00	$28.00	$28.00
10	$34.00	$34.00	$34.00	$34.00
11	$43.00	$43.00	$43.00	$43.00
12	$45.00	$45.00	$45.00	$45.00
13	$48.00	$48.00	$48.00	$48.00
14	$52.00	$52.00	$52.00	$52.00

<tbody>
</tbody>

 

[oldbrewer]

I assume the desired answer is code 7 and country

then $54 as date is before the first date

 

[Johnboy28]

I assume the desired answer is code 7 and country

then $54 as date is before the first date

Yes, the desired answer is Code 7 and unit price is $54. I made a mistake on the first date. Its actually 06/01/2013. Thanks for your help.

Date	Weight Range	Client	Code	Unit $
6/01/2013	362.60	Country	7	$ 54.00

<colgroup><col style="mso-width-source:userset;mso-width-alt:3072;width:63pt" width="84"> <col style="mso-width-source:userset;mso-width-alt:3803;width:78pt" width="104"> <col style="mso-width-source:userset;mso-width-alt:3840;width:79pt" width="105"> <col style="width:48pt" width="64" span="2"> </colgroup><tbody>
</tbody>