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.

[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!