Need a lookup formula

[Scorpvin]

I am looking for a formula that will populate the cells B1:O3. The
source is A13:C27. I need the populated cells to mimic B1:O3. I
thought a VLOOKUP would work but there are to many entries for each
SECID.


+-------------------------------------------------------------------+
|Filename: FormulaFile.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5212 |
+-------------------------------------------------------------------+

 

[cvolkert]

I filled in the section in rows 6:8 using the following formulas - the
first picks up the LotNumber, the second pulls the Amount. The only
difference going this route is that it puts the lots in reverse order -
highest number to lowest. You will need to change the '1' at the end of
the Large function to 2,3 . . . to pick the subsequent lot. Hopefully
this works - unfortunately you can't use the Small function in the same
way since the formula returns 0 for any SECID that doesn't match. Let
me know if you have questions.

=LARGE((($A$14:$A$27=$A6)*$B$14:$B$27),1)

=SUMPRODUCT(($A$14:$A$27=$A6)*($B$14:$B$27=B6)*$C$14:$C$27)

 

[Scorpvin]

Thanks for helping me with the range of B6:C8, but now what formula do I
use for the remaining range of D6:O8?

 

[Scorpvin]

Hey nevermind the last post. Thanks for your help!