# 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

• 39.5 KB Views: 4

#### 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

#### rajkenya1

##### Member
Thanking you so much my friend.

Excellent and works well.

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