John,

The reason is that your range names are all defined to start at row 2 (you
offset the row on Consol.SalesDate by 1 from B1, thereby starting at 2).
However. by using the row multiplier in SP, you use the actual row number,
not that number offset within the defined name range. So row 3 is the 2nd
row in the defined name range, and when you use that value to index in, you
go one row too many.

Either adjust Consol.SalesDat to

=OFFSET(Consol!$B$1,0,0,COUNTA(Consol!$B:$B),1)

or use

=IF(SUMPRODUCT(--(Consol.SalesDate=E$2)*(Consol.Restaurants=1234)*( ROW(Conso
l.Comments)-1))=0,"",INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=E$
2)*(Consol.Restaurants=1234)*(ROW(Consol.Comments)-1))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"John" wrote in message
...
I have the following formula which returns the value +1 Row from the cell
I'm expecting the value to be Returned from


=IF(SUMPRODUCT(--(Consol.SalesDate=E$2)*(Consol.Restaurants=1234)*R OW(Consol
..Comments))=0,"",INDEX(Consol.Comments,SUMPRODUCT (--(Consol.SalesDate=E$2)*(
Consol.Restaurants=1234)*ROW(Consol.Comments))))

The "Consol.Comment" I'm expecting to be returned is what resides in Cell
J2, however it returns what is in J3 and from what I can see, it is always
out +1 Row for all my expected cells

All my range names are dynamic and feed off "Consol.SalesDate" which is
ranged as below

=OFFSET(Consol!$B$1,1,0,COUNTA(Consol!$B:$B),1). As an example the Range
name setting for "Consol.Comments" is =OFFSET(Consol.SalesDate,0,8)

Note all my data starts in Row2, I've Headers in Row1

Am I doing something wrong?


Thanks