NEED HELP creating a formula

[jgui123]

I'm working on an excel spreadsheet and I'm having trouble creating a formula. For someone that uses excel a lot, I'm sure it's easy. Let's see if I can explain...I'm trying to tell cell #1 that IF cell #2 is higher than cell #3 AND cell #4 is higher than value 100 use cell #5, higher than 250 use cell #6, and if higher than 400 use cell #7.

I have attached the file, let's see if I can explain more.

My employees are paid bonus if they beat the goal and have a minimum of 100 Customer Count (CC). Once those two are met, they are paid based on one of three tiers (found on "Categories" tab). The more CC, the more they earn for that category. The sheet is used to calculate Bonus for sales earned. I'm trying to do the formulas on the PAYOUT tab. The requirement for each category (ie. AF, ATS, Etc..) is the quantity sold (found on "All Employee" tab) must be higher than the Goal (on "Categories" tab) AND if "CC" is 100-249 then they get paid from column M on tab CATEGORIES, if 250 - 399, then they get paid from column N, and if 400 or more they get paid from column O. 

[#2]

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.

[#3]

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.

[#4]

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).