Shorten "OR" formula

[Lux Aeterna]

Hello again!

I've got a huge formula that I'd like to shorten, in order to be able to expand cell range more easily if necessary. And make it more readable, of course. Problem is with OR function, as I had to include each cell separately. I tried using OR(C8=W3:W16), but it doesn't seem to work. Thank you in advance!

=IF(C8="";"";IF(OR(C8=$W$3;C8=$W$4;C8=$W$5;C8=$W$6;C8=$W$7;C8=$W$8;C8=$W$9;C8=$W$10;C8=$W$11;C8=$W$12;C8=$W$13;C8=$W$14;C8=$W$15;C8=$W$16;C8=$W$17;C8=$W$18);"Αργία";IF(OR(C8=$N$3;C8=$N$4;C8=$N$5;C8=$N$6;C8=$N$7;C8=$N$8;C8=$N$9;C8=$N$10;C8=$N$11;C8=$N$12;C8=$N$13;C8=$N$14;C8=$N$15;C8=$N$16;C8=$N$17;C8=$N$18;C8=$N$19;C8=$N$20;C8=$N$21;C8=$N$22;C8=$N$23;C8=$N$24;C8=$N$25);$C$39;IF(OR(C8=$O$3;C8=$O$4;C8=$O$5;C8=$O$6;C8=$O$7;C8=$O$8;C8=$O$9;C8=$O$10;C8=$O$11;C8=$O$12;C8=$O$13;C8=$O$14;C8=$O$15;C8=$O$16;C8=$O$17;C8=$O$18;C8=$O$19;C8=$O$20;C8=$O$21;C8=$O$22;C8=$O$23;C8=$O$24;C8=$O$25);"Ρεπό";IF(OR(C8=$P$3;C8=$P$4;C8=$P$5;C8=$P$6;C8=$P$7;C8=$P$8;C8=$P$9;C8=$P$10;C8=$P$11;C8=$P$12;C8=$P$13;C8=$P$14;C8=$P$15;C8=$P$16;C8=$P$17;C8=$P$18;C8=$P$19;C8=$P$20;C8=$P$21;C8=$P$22;C8=$P$23;C8=$P$24;C8=$P$25);"Κανονική";IF(OR(C8=$Q$3;C8=$Q$4;C8=$Q$5;C8=$Q$6);"Γονική";IF(OR(C8=$R$3;C8=$R$4;C8=$R$5;C8=$R$6);"Φοιτητική";IF(OR(C8=$S$3;C8=$S$4;C8=$S$5;C8=$S$6;C8=$S$7;C8=$S$8;C8=$S$9;C8=$S$10;C8=$S$11;C8=$S$12);"Εκπαιδευτική";IF(OR(C8=$T$3;C8=$T$4;C8=$T$5;C8=$T$6;C8=$T$7;C8=$T$8;C8=$T$9;C8=$T$10;C8=$T$11;C8=$T$12;C8=$T$13;C8=$T$14;C8=$T$15;C8=$T$16;C8=$T$17;C8=$T$18;C8=$T$19;C8=$T$20;C8=$T$21;C8=$T$22;C8=$T$23;C8=$T$24;C8=$T$25;C8=$T$26;C8=$T$27;C8=$T$28;C8=$T$29;C8=$T$30;C8=$T$31;C8=$T$32;C8=$T$33);"Αναρρωτική";IF(OR(C8=$U$3;C8=$U$4);"Νόμου 105";IF(OR(C8=$V$3;C8=$V$4;C8=$V$5;C8=$V$6;C8=$V$7;C8=$V$8;C8=$V$9;C8=$V$10;C8=$V$11;C8=$V$12);"Ειδική";$B$39)))))))))))

 

[mikerickson]

Try =IF(ISNUMBER(MATCH(C8, $W$3:$W$18, 0), "Apyia", etc.)

 

[Joe4]

I tried using OR(C8=W3:W16), but it doesn't seem to work.

Try using COUNTIF instead:

=IF(COUNTIF(W3:W16,C8)>0,...)

 

[offthelip]

you can simplify your equation by making a number of changes change this part:

=OR(C8=$W$3,C8=$W$4,C8=$W$5,C8=$W$6,C8=$W$7,C8=$W$8,C8=$W$9,C8=$W$10,C8=$W$11,C8=$W$12,C8=$W$13,C8=$W$14,C8=$W$15,C8=$W$16,C8=$W$17,C8=$W$18)

to

=COUNTIF(W3:W18,C8)>0

 

[DanteAmor]

Try:

varios 23nov2021.xlsm
	D
8	Κανονική
Hoja5

Cell Formulas
Range	Formula
D8	D8	=IF(COUNTIF($W$3:$W$18,C8),"Αργία", IF(COUNTIF($N$3:$N$25,C8),$C$39, IF(COUNTIF($O$3:$O$25,C8),"Ρεπό", IF(COUNTIF($P$3:$P$25,C8),"Κανονική",""))))

 

[Lux Aeterna]

Thanks for all the answers, I'll try them now!

 

[Lux Aeterna]

Ok, I skipped an important info. C8 and W3, 4.... etc are dates.

The concept is that if the date in C8 matches any of the dates in W3:W18 etc. cells, give me the assigned data (the text in Greek).
@DanteAmor @offthelip @Joe4 @mikerickson

 

[DanteAmor]

With the COUNTIF formula, you shouldn't have problems if it's dates or data:

varios 23nov2021.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O
4
5
6													20-nov
7
8	20-nov	Ρεπό
Hoja5

Cell Formulas
Range	Formula
D8	D8	=IF(COUNTIF($W$3:$W$18,C8),"Αργία", IF(COUNTIF($N$3:$N$25,C8),$C$39, IF(COUNTIF($O$3:$O$25,C8),"Ρεπό", IF(COUNTIF($P$3:$P$25,C8),"Κανονική", IF(COUNTIF($Q$3:$Q$25,C8),"Γονική", $B$39)))))

 

[Lux Aeterna]

With the COUNTIF formula, you shouldn't have problems if it's dates or data:

You are right, I had mistyped some of the formula.

However I am still facing a problem.

- Cell C8 sometimes has data (a date) sometimes it is blank.
- Cells W16:18 are intentionally left blank to manually add dates if needed.
-- When C8 is blank it matches with W16:18 and gives me a wrong result ("Αργία" instead of "").

Any bypass for that?

 

[offthelip]

you can add the not blank condition in like this:

AND(COUNTIF(W3:W18,C8)>0,C8<>"")