Nesting Various Ranges Within an IF(COUNTIF( formula

[breakr19]

I'm trying to come up with a formula that will return "4" if the value of Q50 can be found in a certain range.

What that range is, however, will vary depending on only one condition.

So, I have the following:

=IF(COUNTIF("RANGE",Q50),4,0) where I want the "RANGE" to be: N2:N33 if B69=1, N34:N49 if B69=2, N50:N57 if B69=3, N58:N61 if B69=4, and N62:N63 if B69=5.

Is it possible to swap in different ranges in this way? Any help would be appreciate, and happy to answer any clarifying questions. Thanks in advance!

 

[Robert Mika]

named each of your range and then try:Sheet1

*	B	C	G	H	I	M	N	O	P	Q
*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*	A
*	*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*
*	*	*	*	*	*	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:2px;"><col style="width:64px;"><col style="width:64px;"><col style="width:20px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #ffff00"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #ffff00"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]4[/TD]

[TD="bgcolor: #ffff00"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #ffff00"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="bgcolor: #ffff00"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]

[TD="bgcolor: #ffff00"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]

[TD="bgcolor: #ffff00"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]40[/TD]

[TD="bgcolor: #ccccff"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]43[/TD]

[TD="bgcolor: #ccccff"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]44[/TD]

[TD="bgcolor: #ccccff"]A[/TD]

[TD="bgcolor: #cacaca, align: center"]45[/TD]

[TD="bgcolor: #ccccff"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]46[/TD]

[TD="bgcolor: #ccccff"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]47[/TD]

[TD="bgcolor: #ccccff"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]48[/TD]

[TD="bgcolor: #ccccff"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]49[/TD]

[TD="bgcolor: #ccccff"]*[/TD]

[TD="bgcolor: #cacaca, align: center"]50[/TD]

[TD="bgcolor: #cacaca, align: center"]51[/TD]

[TD="bgcolor: #cacaca, align: center"]69[/TD]
[TD="bgcolor: #33cccc, align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]70[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula H5 =IF(COUNTIF(CHOOSE(B69,first,second),Q50),4,0) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[breakr19]

Have never used the CHOOSE feature before, that is exactly what I was looking for, thank you so much!

 

[Robert Mika]

Have never used the CHOOSE feature before, that is exactly what I was looking for, thank you so much!

You are welcome