search for cell value and return row values

[fullymooned]

I am attaching a sheet to explain the problem better

If col E is Y, then result is col D
If col E is N, then search for the same order value in col A in the
rest of the sheet and return col B and col C values for all rows that
contain it

search for cell value and return row values

THANKS

Attachment filename: book1.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=426400

 

[Debra Dalgleish]

If you want to total all the results for the same order, you can use the
following formula in cell F3, and copy down to cell F7 --

=IF(E3="Y",D3,SUMPRODUCT(($A$3:$A$7=A3)*($B$3:$C$7)))