Lookup by reference to months?

  • Thread starter Thread starter Elijah
  • Start date Start date
E

Elijah

Hi,

At work I've been asked to restructure a XLS so that we can work out future
sales based on price changes. The first sheet I want to use for inputting
the current prices for various products as well as forecast prices. Eg

Mar-03 Jan-05 Apr-05 Jun-05
Product A $1 $2 $2.5 ..
Product B $2 $2.5 $3.0 ..

In sheet 2 I have the annual projections on a monthly basis starting from
Jul-04 for the next 10 years, with each month going across the second row.
What I need to do is basically multiple price by quantity. I have the future
quantities but I somehow need to pickup the relevant future prices depending
forecast month. For example in the months of Jan-05, Feb-05, Mar-05 I want
to be able to pick up $2 as the price for product A, until it changes in
April 2005.

Can anyone advise on which LOOKUP function to use?
Any help/suggestions appreciated.

Elijah
 
Hi Elijah!

Ignoring your Mar-03 column and assuming that Jan-05 and the similar
dates on Sheet2 are Excel dates serial numbers, I get the following to
work OK.

=VLOOKUP($A2,Sheet2!$A$2:$DW$3,DATEDIF("1-Jul-2004",C$1,"m")+2)

$A2 ensures locking on to the Product column
Sheet2!$A$2:$DW$3 locks on to a table with Products in the A2:A3 and
inflated prices in columns B to DW under the months in B1:DW1

DATEDIF with the third argument of "m" gives us the number of months
between two dates which are your base date of Jul-2004 and the date
you have in row 1 of your Sheet1. Adding 2 gives me the column to look
for.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top