Need to fetch data based on multiple conditions

[Junarkar]

Hi,

I need to calculate incentive based on conditions from 2 tables. Sample file attached.

Incentive need to come on Sales data sheet.

There are two slabs for achievement - For 100% and more and for less than 100%.

Achievement - Will tell group wise achievement.

Example;
Group APL achievement - 100%
Product ID - 267569
So staff is eligible for Rs. 94 incentive

Like this incentive amount should come against every product so that I can calculate total incentive for each staff.

Not every product is eligible for incentive.

Thanks in advance.

 

[vletm]

Junarkar​

Your So staff is eligible for Rs. 94 incentive
... how about 113? It have same Product ID.
Could You show Your expected verified results?

 

[bosco_yip]

2 simply Vlookup function is enough for your work.

In D2, formula copied down:

=IFERROR(VLOOKUP(B2,Incentive!$C$2:$E$13,(VLOOKUP(A2,Achievement!$A$2:$B$4,2,0)>=1)+2,0),"")