# Need a Formula to Review Two Criteria and Return Multiple Values

Copper Contributor

# Need a Formula to Review Two Criteria and Return Multiple Values

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)
• 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                        False       True     = Reg Fee Only

6 Replies

# Re: Need a Formula to Review Two Criteria and Return Multiple Values

You can try this nested IF formula.

# Re: Need a Formula to Review Two Criteria and Return Multiple Values

@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?

# Re: Need a Formula to Review Two Criteria and Return Multiple Values

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?

# Re: Need a Formula to Review Two Criteria and Return Multiple Values

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

# Re: Need a Formula to Review Two Criteria and Return Multiple Values

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

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

# Re: Need a Formula to Review Two Criteria and Return Multiple Values

@OliverScheurich, thank you!!! That worked!