Damn Lookup Problem Again

  • Thread starter Thread starter John
  • Start date Start date
J

John

Yet another of my Lookup problems. I am trying to return Sales for Last Year
for a Specified Restaurant. My data is located as follows in Sheet2

Store(A1:...) Sales Date (B1:......) Sales Value (C1:......)

On sheet1 I have a Store Reference in A6 that will equate to one of 30
stores in Column A in Sheet1 (30 stores by 360 days). I also have a
reference date in D1 that gives me the current years date eg. today is
03/06/2004

I want to return in Cell D6 on Sheet1 last Years actual Sales for Store No.1
(which has its reference in A6) for the date in D1. I will continue the for
the rest of the week for Store No. 1-30

In a way I have 2 lookups. eg. Lookup the Store No. that is referenced in
A6, then depending on the date in D1 return the equivalent day last year -
which will be that date minus 366

This Lookup function is driving me nuts

Thanks
 
=SUMPRODUCT((Sheet2!$A$1:$A$1000=$A$6)*(Sheet2!$B$1:$B$1000=$D$1)*(Sheet2!$C
$1:$C$1000))
 
Thanks for your reply Vasant, can't see why I would use Sumproduct if there
is one date per store and one sales value per store per date

Rgds
 
Hi John,

I'm not sure that this is what you want, but here goes! I'm assuming
that your store reference starts at A6 with 1 and continues down the
column for all 30 stores (I wasn't to clear on that.) I'm also assuming
that your data on Sheet2 starts in row 2. Try entering this formula in
D6 and copy it down as far as you need to:

=INDEX(Sheet2!$C$2:$C$100,MATCH(1,(Sheet2!$A$2:$A$100=Sheet1!A6)*(Sheet2!
$B$2:$B$100=DATE(YEAR($D$1)-1,MONTH($D$1),DAY($D$1))),0))

to be entered using CTRL+SHIFT+ENTER

adjust the range for the data in sheet2

Hope this helps!
 
Thanks Domenic for the reply

I have range names setup for each of my data 'columns' i.e. Restaurants;
Dates; Sales so I have modified your formula as follows

=INDEX(Sales,MATCH(1,(Restaurants=A6)*(Dates=DATE(YEAR($D$1)-1,MONTH($D$1),D
AY($D$1))),0))

But I'm getting a #N/A. Looking within the help for this function, it shows
me the Formula Result to be exactly the Sales for 31/5/2003, but when I hit
return it shows #N/A. So its nearly correct except for the #N/A and the
sales should be 30/5/2003 - as I'm trying to match against the same
'business day'
 
Hi John,

John said:
but when I hit
return it shows #N/A.
Click to expand...

Are you making sure that after entering the formula that you hit
CTRL+SHIFT+RETURN, and not just RETURN?
 
Aaaaaaah - that's it Domenic, I was unaware of that feature, it just pops it
parentheses - right?

Thanks again
 
Back
Top