Need a Formula to Review Two Criteria and Return Multiple Values

[Holly_Michaelson]

I need to write a "complicated" formula and don't have the first idea of what function to use so searching is not helping me. I'm down a rabbit hole and in need of help! 

Gist:

• I have three columns (L and AE and AI) that I need to review and produce a text entry into AF.
• There are three possible values in L. Two of them can be treated together. One of them is unique.
  • Teamraiser Registration (differentiator)
  • Teamraiser Gift (same as Sustaining Gift)
  • Sustaining Gift (same as Teamraiser Gift)
• There are two possible values in AE. True or False
• There are two possible values in AI. True or False.
• I need the formula in AF to vary, based on the combination of what is found in L and AE and AI combined. The output should be a short text string, based on the 6 possible combinations. (Assuming I can combine two values as a single option when reading L. 

For example:

L                                                           AE           AI            AF

Teamraiser Registration                        True        True    = Additional Gift

Teamraiser Registration                        False       True     = Reg Fee Only

Teamraiser Gift or Sustaining Gift         True        True    = Later Gift

Teamraiser Gift or Sustaining Gift         True        False   = Other Donor

Teamraiser Gift or Sustaining Gift         False       True    = Later Gift

Teamraiser Gift or Sustaining Gift         False       False   = Other Donor

[#2]

@Holly_Michaelson 

=IF(AND(L2="Teamraiser Registration Gift",AE2="True",AI2="True"),"Additional",

IF(AND(L2="Teamraiser Registration Gift",AE2="False",AI2="True"),"Reg Fee",

IF(AND(OR(L2="Teamraiser Gift",L2="Sustaining Gift"),OR(AE2="True",AE2="False"),AI2="True"),"Later Gift",

IF(AND(OR(L2="Teamraiser Gift",L2="Sustaining Gift"),OR(AE2="True",AE2="False"),AI2="False"),"Other Donor",""))))

You can try this nested IF formula.

[#3]

@OliverScheurich

Thank you for taking the time to create this complicated formula. I would never have figured that out! I apologize for my delayed response. I had set the projcect aside when I got stuck and have finally resumed today.

I used this formula, but I get nothing but blanks. I don't get any values in column AF, and no error messages about the formula.

Cannot seem to figure out what I am doing wrong. Mind helping again?

IF(AND(L2="Teamraiser Registration Gift",AE2="TRUE",AI2="TRUE"),"Additional",IF(AND(L2="Teamraiser Registration Gift",AE2="FALSE",AI2="TRUE"),"Reg Fee",IF(AND(OR(L2="Teamraiser Gift",L2="Sustaining Gift"),OR(AE2="TRUE",AE2="FALSE"),AI2="TRUE"),"Later Gift",IF(AND(OR(L2="Teamraiser Gift",L2="Sustaining Gift"),OR(AE2="TRUE",AE2="FALSE"),AI2="FALSE"),"Other Donor",""))))

[#4]

@Holly_Michaelson 

=IF(AND(L2="Teamraiser Registration Gift",AE2="TRUE",AI2="TRUE"),"Additional",IF(AND(L2="Teamraiser Registration Gift",AE2="FALSE",AI2="TRUE"),"Reg Fee",IF(AND(OR(L2="Teamraiser Gift",L2="Sustaining Gift"),OR(AE2="TRUE",AE2="FALSE"),AI2="TRUE"),"Later Gift",IF(AND(OR(L2="Teamraiser Gift",L2="Sustaining Gift"),OR(AE2="TRUE",AE2="FALSE"),AI2="FALSE"),"Other Donor",""))))

I've translated the formula into german and back into english and it returns the expected result in my sheet. Maybe you can attach a sample workbook without sensitive data or a screenshot without sensitive data which shows the formula, the ranges and the results in your sheet?

[#5]

Hi @OliverScheurich! The formula you gave @Holly_Michaelson worked out for me, in my scenario. I do have a slight difference I am trying to achieve and could not figure out the correct way to manipulate it. I in turn would like to return a value based on the what the text is in the referenced field, so if it says "COOL, WARM, or HOT", I want it to return "Chill" for cool, "Nice" for Warm, or "Bad" for Hot. Would you know how I can set this up in a formula in excel?
Ex: 

Cool	= "Chill"
Warm	= "Nice"
Hot
Warm
Cool
Cool
Hot
Warm
Hot

[#6]

Hi, @andii2617 you can try this nested IF formula.

=IF(A1="Cool","Chill",IF(A1="Warm","Nice",IF(A1="Hot","Bad","")))

[#7]

@OliverScheurich, thank you!!! That worked!