Lookup rate based on vendor and date range

**June12** · 06-30-2011, 04:37 AM

Hi~~ i need help on the below.. I have a set of data.. Based on vendor and date range (validity of rates)

I need a formula to help extract rates based on vendor and shipment date.

Carrier & Port From till Rate
MaerskAKL 1-Jun-11 30-Jun-12 100
NYKAKL 1-Jun-11 2-Jun-12 200
OOCLAKL 3-Aug-11 3-Jun-12 300
APLAKL 5-Aug-11 4-Jun-12 400

Shipment is on 8 Jul under NYKAKL.

How to vlookup for the rates since I have date range but yet I am not able to combine the date range to do a normal vlookup?

**Azam Ali** · 06-30-2011, 05:07 AM

Kindly see the enclosed file.

If it does not meet your requirment. Kindly upload a smaple for further explanation of your issue.

**outofthehat** · 06-30-2011, 05:31 AM

I see you're using 2003. If 2007/2010 I'd say use SUMIFS function, but you don't have that option. However, you can use array formulae to achieve the same. See attached. You get the curly brackets round an array formula by hitting ctrl-shift-enter, instead of just enter.