Looking up a MAX Date within a Range Q

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

John

Could anyone assist me with an error I'm having with the formula below, it
returns a #N/A.

What I'm trying to achieve is the return of the Closing Stock of Product
that is detailed in H6, for location "3047". The reason that I'm using the
MAX date between "Input!AA1" and "Index!AA2" detailed within the range name
"SalesDate" is because my data that contains the closing stock also contains
other stock movements and the 'youngest date' is the most relevant closing
stock

=INDEX(Closing_Stocks,MATCH(MAX((StoreNo=3047)*(Inventory_No=$H$6)*(SalesDate>=Input!AA1)*(SalesDate<=Input!AA2)*SalesDate),SalesDate,0))

I've checked that all my range names are the same length


Thanks
 
It's nothing wrong with your formula per se so it must be either of these 3

1. Your MAX part returns something that cannot be found in the SalesDate
range thus returning #N/A

2. Your named ranges are of different size (one of them is all it takes)

3. You have an #N/A error in one of your ranges
 
Thanks Peo

When I go into Evaluation the #N/A appears for the named range
"Inventory_No". This has been created using the following offset
"=OFFSET(StoreNo,0,2)". The StoreNo has been created using "=OFFSET('Sales
Mix'!$A$2,0,0,COUNTA('Sales Mix'!$A:$A),1)". Similarly the other named
ranges in my formula use the same offset type formula. I have some zeros in
some of my rows but don't think that makes any difference.

Going back to the Evaluation, for the named range "Inventory_No", it returns
14 - don't know why it would do such, as I have product #1 selected in H6.
If I change the selected product to 14 in H6, then the formula returns the
closing stock of the first row in my database - not the closing stock of
product #14

There are no #N/A errors in my range

Just to clarify, the formula should return the closing stock for the
selected product in H6, for location 3047, based on the latest date within
my database (which is referenced by the range name Closing_Stocks)
 
John,

For information, it is an array formula, so it needs to be committed with
Ctrl-Shift-Enter, else it returns a #N/A. But even this doesn't fix it, as
it then seems to ignore the Inventory_No = $H$6, returning the same answer
regardless of what is in $H$6.

Evaluating the formula gives an array as I would expect, not a value as you
get.

Peo, all of the name ranges are based upon Store, so for instance
Closing_Stock is defined as =OFFSET(StoreNo,0,10). This will keep them all
the same size as far as I can see.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Problem solved, thanks to all


Bob Phillips said:
John,

For information, it is an array formula, so it needs to be committed with
Ctrl-Shift-Enter, else it returns a #N/A. But even this doesn't fix it, as
it then seems to ignore the Inventory_No = $H$6, returning the same answer
regardless of what is in $H$6.

Evaluating the formula gives an array as I would expect, not a value as
you
get.

Peo, all of the name ranges are based upon Store, so for instance
Closing_Stock is defined as =OFFSET(StoreNo,0,10). This will keep them all
the same size as far as I can see.

--

HTH

RP
(remove nothere from the email address if mailing direct)
Click to expand...
 
Back
Top