Forum Discussion
NEED HELP creating a formula
Hi,
Data is not very well organized - lot of repeating plus different names for the same (BAT and BATT; ADDITIVE and ADDITIVES).
To simplify I added category abbreviations in column I (in general they could be picked-up from previous columns), the formula with that could be like (for F3)
=IFNA(INDEX(Categories!$M$2:$O$20,MATCH(F$2,Categories!$I$2:$I$20,0),LOOKUP($E3,Categories!$L$1:$O$1,{0,1,2,3})),0)
drag it down and to the right.
I understand the organization of the data, sorry for that. First, I truly appreciate your response, thank you so much. However, this did not work. If you look on tab ALL EMPLOYEES you will see that "Additives" were not hit as the goal was 3 but the additives sold were 0. On the PAYOUT tab he should not have received $25.
Sorry, forgot about goals limit concentrating on CC.
You may wrap previous formula with if goal met or not, like
=IF('All Employee'!F4>=F$1,IFNA(INDEX(Categories!$M$2:$O$20,MATCH(F$2,Categories!$I$2:$I$20,0),LOOKUP($E3,Categories!$L$1:$O$1,{0,1,2,3})),0),0)To simplify I took data from related All Employee cell (much better INDEX/MATCH employees by ID or another unique field) and took Goal from Pay Out tab (Goals are repeating here from Categories, better to lookup them based on category abbreviation).