SUMPRODUCT to return first instance

[tarynmahon]

I have the following formula;
=SUMPRODUCT(--([Lookupsheet]A:A=A1),--([Lookupsheet]B:B=B1),--([Lookupsheet]C:C=C1),[Lookupsheet]D:D)

As there are more than one row that matches this criteria my formula is adding all the instances in "D" together, they're all the same so I just want to return the first instance, is there a way of doing this please?

 

[Somendra Misra]

@tarynmahon

Can you upload a sample workbook?

Regards,

 

[tarynmahon]

No sorry I cannot as my permissions at work dont allow, I've simplified the formula so that its generic you should be able to work it out but please ask if you need me to explain anything else

 

[Somendra Misra]

@tarynmahon
Use a similar formula by changing range.
=INDEX(D1:D9,SMALL(IF((--(A1:A9=G1))*(--(B1:B9=H1))*(--(C1:C9=I1)),ROW($A$1:$A$9)-ROW($A$1)+1),1))

Just advise if any issue.
Regards,

 

[tarynmahon]

Thank you

 

[Somendra Misra]

Welcome anytime.