if formula with multiple choices

[qv2]

in a testing setting, i have several 'wrong' answer question numbers that refer to a particular subject....ie if you miss questions 1,3,5,7,9 you need to work on pronouns.

how can i get an excel 'if' function to return 1 value for several selections? i envision the following:

if(a1= 1 or 2 or 3 or 4 or 5, "pronoun","")

but of course that doesn't work, nor does 1,2,3,4

any help?

 

[MrKowz]

Welcome to the forums!

Try:

Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">2</td><td style=";">Pronoun</td></tr></tbody></table>

Sheet1​

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" width="85%" cellpadding="2.5px"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6" width="100%" cellpadding="2.5px"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">B1</th><td style="text-align:left">=IF(OR(A1={1,2,3,4,5}),"Pronoun","")</td></tr></tbody></table></td></tr></tbody></table>

 

[Matty]

Welcome to the Board...

If I follow correctly, perhaps:

=IF(OR(A1={1,2,3,4,5}),"pronoun","")

Matty

 

[bonnaru]

The two previous seem like they would work.
also, stacking if statements would do the trick.

=if(1,"pronoun",if(2,"pronoun",if(3,"pronoun",....,"")))

 

[MrKowz]

The two previous seem like they would work.
also, stacking if statements would do the trick.

=if(1,"pronoun",if(2,"pronoun",if(3,"pronoun",....,"")))

Welcome to the forums, Bonnaru!

Yes, that would work (though you still need to say A1=1, A1=2, A1=3, etc...). However, in Excel 2003 and older, you have a limitation of seven nested statements. Usually when comparing a single cell to a list of values, you want to use an OR statement, a CHOOSE statement, or a LOOKUP statement... though it all depends what you desire as your output.

 

[qv2]

holymoley...you guys are fast! it nesting with {} worked like a charm so far. thank you!!!!!!

any idea in 2007 how many choices i can put in the {}?

 

[MrKowz]

holymoley...you guys are fast! it nesting with {} worked like a charm so far. thank you!!!!!!

any idea in 2007 how many choices i can put in the {}?

As many as you want, however depending on what your actual conditions are, we might be able to build a nice, tidy, efficient formula.

Can you please list out your entire scenario that you want this formula to cover?

 

[T. Valko]

holymoley...you guys are fast! it nesting with {} worked like a charm so far. thank you!!!!!!

any idea in 2007 how many choices i can put in the {}?

I'm not sure how many elements can be used like that.

However, if you have more than a "few" it'd be better to list the variables in a range of cells and then just refer to that.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 63px"><COL style="WIDTH: 63px"><COL style="WIDTH: 63px"><COL style="WIDTH: 63px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">Pronoun</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">5</TD></TR></TBODY></TABLE>


Formula entered in B1:

=IF(COUNT(MATCH(A1,D1:D5,0)),"Pronoun","")

 

[qv2]

ok, let's up the ante if i can.

good with missing questions 1,2,3,4,5 = pronoun in a1

BUT

how about if in a1 another choice was missing 6,7,8,9 = noun

so now a1 has 9 choices to enter with 2 returns, pronoun or noun....yes????

 

[T. Valko]

ok, let's up the ante if i can.

good with missing questions 1,2,3,4,5 = pronoun in a1

BUT

how about if in a1 another choice was missing 6,7,8,9 = noun

so now a1 has 9 choices to enter with 2 returns, pronoun or noun....yes????

Create a 2 column lookup table:

Book1
	A	B	C	D	E
1	6	Noun	_	1	Pronoun
2	_	_	_	2	Pronoun
3	_	_	_	3	Pronoun
4	_	_	_	4	Pronoun
5	_	_	_	5	Pronoun
6	_	_	_	6	Noun
7	_	_	_	7	Noun
8	_	_	_	8	Noun
9	_	_	_	9	Noun
Sheet1

Formula entered in B1:

=VLOOKUP(A1,D1:E9,2,0)

Or possibly:

=INDEX(E1:E9,A1)