Complicated nested if formula for weapons qualification.

I have tracker I'm using for weapons qualification but I know ther has to be a smater way to do it. I have a formula that is giving "too many arguments for this function" error. Basically the formula is supposed to determine the type of weapon in "D2" from a pull down menu it then reads the score in "F2" and compares it to the criteria for a "rating".

=IF(OR(D2="M4T",D2="M16T"),IF(F2>35,"EXP",IF(F2>29,"SS",IF(F2>22,"MM",IF(F2>=1,"UNQUAL","")))),IF(D2="M4P",D2="M16P"),IF(F2>37,"EXP",IF(F2>32,"SS",IF(F2>25,"MM",IF(F2>=1,"UNQUAL","")))),IF(D2="M249",IF(F2>182,"EXP",IF(F2>158,"1st",IF(F2>133,"2nd",IF(F2>=1,"UNQUAL","")))),IF(D2="M240B",IF(F2>206,"EXP",IF(F2>180,"1st",IF(F2>158,"2ND",IF(F2>=1,"UNQUAL","")))),IF(D2="M203",IF(F2>80,"EXP",IF(F2>70,"1st",IF(F2>60,"2ND",IF(F2>=1,"UNQUAL","")))),IF(D2="M9",IF(F2>159,"EXP",IF(F2>119,"SS",IF(F2>79,"MM",IF(F2>=1,"UNQUAL","")))),IF(D2="M2 .50 CAL",IF(F2>195,"EXP",IF(F2>173,"1ST",IF(F2>152,"2ND",IF(F2>=1,"UNQUAL","")))),"")))))

I would like to be able to do the same thing with either more simple formula or a type of lookup wich is not my strong suit I want to also add more weapons type..

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

It would be a lot easier to suggest an alternative if you posted a workbook so people could see what you're trying to do.

Still not really brilliant, have you bothered to read the Forum Rules (LOL)?

Something like "complicated nested if formula" would be better and no abbreviations we have a lot of members whose first language is not English & they may not follow abbreviations

Thanks for posting the example.

Now that you have, would you explain in narrative fashion what you're trying to do that led you to this formula?

EDIT: I think it can all be replaced by a simple table lookup that contains ratings by weapon and score.

See attached.

Thanks shg. It's what I'm looking for, however when I try to use it on my document I get a #Name? error. I cut and pasted the fomula and then dropped the table into my data sheet. I guess I should have said that this was part of a a multi sheet workbook. I cannot post the workbook complete. Where do i drop the table containing the rating criteria?

It's part of a dynamic named range. Do Insert > Name > Define to see the definitions.

Maybe you could add new data and/or sheets to the example to transform it to what you need.

Good job! Glad the cat survived.

Originally Posted by SavageMind

I have read the rules. This is not my first post either. I am try to articulate my issue the best way I can. The "brilliant" remark I can do without. I have attached an example of what I have now wthout the error but without the addtional weapon systems.

Sorry you don't like being told that you haven't followed the rules correctly. If you don't break them then it won't happen. You are lucky that the thread wasn't simply closed. Checking back on your previous posts it sems you need to pay attention to the thread title rule