MrExcel Publishing
Your One Stop for Excel Tips & Solutions

multiple variables


Posted by Monica on February 06, 2000 11:58 AM

Hi everyone.

I have a question regarding multiple variables. This is a little complicated so please bear with me. Say column A can have one of 4 different values, column B can have one of 5 different values, and column C can have one of 4 different values as well. All these are different ie A denotes percentages, B denotes numbers, and C denotes abbreviations. Can i set up a formula that produces a result in say column D, if A, B and C together fulfill a particular condition, or yet another value if they don't? There are several combinations that we need to "flag", and i want to set up my spreadsheet so that whenever any of those particular combinations occur, i see a "flag" in column D. Can anyone help me at all? I'd really appreciate it.

Thank you.

Monica


Posted by Celia on February 06, 2000 6:25 PM


Monica
Let's say that you want to identify matches of "23%" in column A, the number “99” in column B and "Abbrev" in column C.
Put the following array formula in cell D1 and drag it down your list.
The result in column D will be “Yes” for matches and “No” for non-matches.

=IF((AI=23%)*(B1=99)*(C1=”Abbrev”),”Yes”,”No”)

If you need more than one conditional match, just add more IF’s to the formula.

Celia