Thats exactly what I want, thanks for your help Bernie


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
John,

This returns "" when the corresponding comment is blank, otherwise it
returns the comment string:

=IF(INDEX(Consol.Comments,SUMPRODUCT(--
(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Con sol.Comments)))=0,
"",INDEX(Consol.Comments,SUMPRODUCT(--
(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Con sol.Comments))))

HTH,
Bernie
MS Excel MVP


"John" wrote in message
...
Okay this is getting confusing Bernie, from a brief sample your code
seems to work, but when Consol.Comments = <blank instead of return
<blank as per your formula it returns Zero


"John" wrote in message
...
I just can't get it to work Bernie, it returns Zero now where I expect a
value, not sure if I'm explaining it correctly


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
If I understand correctly, try:

=IF(SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Con sol.Comments))=0,"",INDEX(Consol.Comments,SUMPRODU CT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Con sol.Comments))))

HTH,
Bernie
MS Excel MVP


"John" wrote in message
...
Bernie, I'm getting inconsistent returns, where I expect a certain
value it returns correct. I've amended your formula slightly to
account for Zero's and thus return <blank, not sure why I am not
getting the correct result

=IF(INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Con sol.Comments)))=0,"",INDEX(Consol.Comments,SUMPROD UCT(--(Consol.SalesDate=D$1)*(Consol.Area=12345)*ROW(Con sol.Comments))))


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
John,

Something along the lines of

=INDEX(Consol.Comments,SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR")*ROW(Con sol.Comments))

will work, if comments is a range that starts in row 1 - otherwise,
you need to subtract an offset...

HTH,
Bernie
MS Excel MVP


"John" wrote in message
...
I want to return a value in a database listing if 2 conditions apply.
This value is Text. I have the following formula which works great
when I am search for a numeric field but doesn't work when the value
is Text

=SUMPRODUCT(--(Consol.SalesDate=D$1)*(Consol.Area="EUR"),((Conso l.Comments)))

The Consol.Comments range is the value I am trying to return. If I
use the above formula it returns 0, not the correct answer