1. ## Lookup Values (2002)

I'm having problems trying to sort out a lookup formula that will collect values based on selected dates. The attached spread sheet explains it. I'm sure it is a simple exercise for a Guru out there - Thanks for your help

2. ## Re: Lookup Values (2002)

Try
This looks up the month (C18) in the first row of AD3:AP9, and returns the corresponding value from the 3rd row of this range. For Bolts, replace 3 by 4 etc. If you would like the formula to look this up too, use

3. ## Re: Lookup Values (2002)

Hans, That's great Thanks. The other thing I would like to do is to sum the total number of Bolts for the 12 month period ie, Jan-03 to Jan-04 based on the lookup date. Any thoughts?

4. ## Re: Lookup Values (2002)

Try this in cell AD19 and copy it down for each item:

=SUM(OFFSET(\$C\$3,MATCH(\$B19,\$C\$5:\$C\$9,0)+1,MATCH(\$ C\$18,\$D\$3:\$AP\$3,0),1,-12))

You can make that last -12 refer to another cell if you want to vary the number of months to sum.

By the way, 12 months is really February to January, not January to January.

5. ## Re: Lookup Values (2002)

Try this array (confirm with ctrl-shift-enter)

Expand the ranges as needed

Steve