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
3	*	*	*	*	*	*	*	*	*	*
4	*	*	*	*	*	*	*	*	*	*
5	*	*	*	4	*	*	*	*	*	*
6	*	*	*	*	*	*	*	*	*	*
17	*	*	*	*	*	*	*	*	*	*
18	*	*	*	*	*	*	*	*	*	*
19	*	*	*	*	*	*	*	*	*	*
40	*	*	*	*	*	*	*	*	*	*
43	*	*	*	*	*	*	*	*	*	*
44	*	*	*	*	*	*	A	*	*	*
45	*	*	*	*	*	*	*	*	*	*
46	*	*	*	*	*	*	*	*	*	*
47	*	*	*	*	*	*	*	*	*	*
48	*	*	*	*	*	*	*	*	*	*
49	*	*	*	*	*	*	*	*	*	*
50	*	*	*	*	*	*	*	*	*	A
51	*	*	*	*	*	*	*	*	*	*
69	2	*	*	*	*	*	*	*	*	*
70	*	*	*	*	*	*	*	*	*	*

<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>
</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