I am trying to calculate bonuses based on provider type and location, and input a formula to calculate this. Although it works well for two of the variables "high" and "low" need, it will not return a value for "moderate" need. I can change the other value locations and it produces the same error, regardless of where the data is located whenever I use "moderate". I attached a print screen of the area of the spreadsheet containing the formula, as well as the document. Any suggestions or ideas would be appreciated! Thanks.



Array error.jpg

Salary Worksheet.xlsx

You have trailing spaces after the word "Moderate" in column N and after MD/DO in column P.

Also, you should not use full column ranges in Array formula as they are processor expensive.