There has to be a Simpler formula?

[m_in_spain]

Hi (again)
I am sure this is something to do with array or range, but i cannot get my head around it.

There must be a simpler way to express the "if(or(" part of the following formula (in red).

=IF(qcountry=CA1,1,IF(OR(qcountry=CA2,qcountry=CA60,qcountry=CA81),3,IF(OR(qcountry=CA5,qcountry=CA6,qcountry=CA7,qcountry=CA8,qcountry=CA9,qcountry=CA10,qcountry=CA11,qcountry=CA12,qcountry=CA13,qcountry=CA14,qcountry=CA15,qcountry=CA16,qcountry=CA17,qcountry=CA18,qcountry=CA19,qcountry=CA20,qcountry=CA21,qcountry=CA22,qcountry=CA23,qcountry=CA24,qcountry=CA25,qcountry=CA26,qcountry=CA27,qcountry=CA28,qcountry=CA29,qcountry=CA30,qcountry=CA31,qcountry=CA32,qcountry=CA33,qcountry=CA34,qcountry=CA35,qcountry=CA36,qcountry=CA37,qcountry=CA38,qcountry=CA39,qcountry=CA40,qcountry=CA41,qcountry=CA42,qcountry=CA43,qcountry=CA44,qcountry=CA45,qcountry=CA46,qcountry=CA47),2,4)))

it is just a consecutive list from CA5 to CA47 This works but it is cumbersome

any help will be much appreciated,
TIA

 

[jtakw]

Hi,

I don't know what qcountry refers to, but see if this works for you:

Book1
	J
2	#NAME?
Sheet354

Cell Formulas
Range	Formula
J2	=IF(qcountry=CA1,1,IF(OR(qcountry=CA2,qcountry=CA60,qcountry=CA81),3,IF(COUNTIF(CA5:CA47,qcountry),2,4)))

 

[m_in_spain]

Many thanks, yes that's exactly what i have been trying to do all day!!
qcountry is a list of countries that i have in my quote (q) - i have to keep this simple to get my head around it.
Again, many thanks for your answer

 

[jtakw]

You're welcome.