• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Timing of Purchase and return value in corresponding year

rajkenya1

Member
Hi

I am trying to get a formula in say cells D4 to R4 which will return the value in cells B7 to B11 based on the year it occurred.

Say for example from data highlighted in the table in orange (from Cells A7 to B11) in year 3 5 units were sold therefore this number should appear in cell F4. Now incase i change cell A7 from Year 3 to Year 15 (cell R15) then that number should move to cell R15 there and leave cell F4 blank.
 

Attachments

bosco_yip

Excel Ninja
Try,

In D4, formula copied across right to R4 :

=IFERROR(INDEX($B$7:$B$11,MATCH(D$3,$A$7:$A$11,0)),"")

or,

=IFERROR(VLOOKUP(D$3,$A$7:$B$11,2,0),"")

Regards
 
Top