I have a spreadsheet where in cell C2 the user chooses from 14 different options. In cell C13 the user again chooses from 3 different options. In cell D13 I want a score to appear that is the result of the option chosen from cell C2 and C13.
Example:
In cell C2 I have a table that provides different types of roles such as, Supplier, Charity/Sponsorship, Real Estate Landlord, International Consultant, Domestic Sales Intermediary, Distributor, Reseller, Offset Partner, Joint Venture, Offset Service Provider, Customer, Joint Venture, Prime Contractor and International Representative where the user chooses one of the options.
In cell C13 I have a table that you can choose one of the options, ‘Under $1M USD’, ‘$1M to $4.9M USD’, and ‘Greater than $5M USD’.
In cell D13 I want a value to be identified based on the following criteria:
If the answer in cell C2 equals the roles ‘Supplier, Charity/Sponsorship, Real Estate Landlord, International Consultant, Domestic Sales Intermediary, Distributor, Reseller, Offset Partner, Joint Venture or Offset Service Provider’ and the answer in cell C13 is ‘$1M to $4.9M USD’, or ‘Greater than $5M USD’, then it will receive a score of 15, if it is ‘Under $1M USD’ it gets a score of 1. If the answer in cell C2 is ‘International Representative’ and cell C13 is ‘Greater than $5M USD’ than it gets a score of 15, if its either ‘Under $1M USD’, or ‘$1M to $4.9M USD’ then it gets a score of 1.
If the answer in cell C2 is ‘Customer, Teaming or Prime Contractor’ regardless of the dollar value chosen in cell C13 the score is zero.
Searching the web it appears that I should be using the nested IF function. I have tried several variations of the =IF and =IFS with OR and AND nested formulas with no success.
Can someone write the formula for me and explain if I am using the wrong function, or maybe it is that I am using a table. I have even tried using defined names.
