EXCEL FORMULA IF OR

[anshikam]

Hi All,

I below formula is not working.

Any help would be appreciated.

=IF(OR(Q3="DELUXE",Q3="DELUXE (CRADLE)", Q3="FOUR SHARING",Q3="PRIVATE ROOM",Q3="TWIN SHARING",Q3="TWIN SHARING AC ROOM",Q3="TWIN SHARING A/C ROOM"), "DELUXE", IF(OR(Q3="GENERAL WARD -1", Q3= "GENERAL WARD -2", Q3="TRIPLE SHARING A/C ROOM"), "COVID", IF(OR(Q3="ICU - 1", Q3="ICU - II", Q3="ICU - III",Q3="Pvt ICU I", Q3="Pvt ICU III"),"ICU", IF(OR(Q3="MATERNITY AND GYNAE WARD", Q3= "MATERNITY PRIVATE ROOM (CRADLE)”, Q3= “MATERNITY PVT. ROOM", Q3= "MATERNITY TW SH - A/C (CRADEL)",Q3="MATERNITY TWIN SHARING A/C", Q3= "MATERNITY WARD (CRADLE)"), "MATERNITY", IF(OR(Q3="MALE GENERAL WARD", "MGW", IF(OR(Q3="NICU -1B", Q3="NICU -2B", Q3="PAEDIATRIC GENERAL WARD", Q3="PICU -A", Q3="PICU -B", Q3="PICU -C", Q3="PRIVATE ROOM(CRADLE)", Q3="TWIN SHARING (CRADLE)", "PGW", IF (OR(Q3="FEMALE GENERAL WARD"), "FGW", "NA")))))))

 

[anand3dinesh]

you have missied some paranthesis. i have fixed it check below

=IF(OR(Q3="DELUXE",Q3="DELUXE (CRADLE)", Q3="FOUR SHARING",Q3="PRIVATE ROOM",Q3="TWIN SHARING",Q3="TWIN SHARING AC ROOM",Q3="TWIN SHARING A/C ROOM"), "DELUXE", IF(OR(Q3="GENERAL WARD -1", Q3= "GENERAL WARD -2", Q3="TRIPLE SHARING A/C ROOM"), "COVID", IF(OR(Q3="ICU - 1", Q3="ICU - II", Q3="ICU - III",Q3="Pvt ICU I", Q3="Pvt ICU III"),"ICU", IF(OR(Q3="MATERNITY AND GYNAE WARD",Q3= "MATERNITY PRIVATE ROOM (CRADLE)",Q3="MATERNITY PVT. ROOM", Q3= "MATERNITY TW SH - A/C (CRADEL)",Q3="MATERNITY TWIN SHARING A/C", Q3= "MATERNITY WARD (CRADLE)"), "MATERNITY", IF(OR(Q3="MALE GENERAL WARD"), "MGW", IF(OR(Q3="NICU -1B", Q3="NICU -2B", Q3="PAEDIATRIC GENERAL WARD", Q3="PICU -A", Q3="PICU -B", Q3="PICU -C", Q3="PRIVATE ROOM(CRADLE)", Q3="TWIN SHARING (CRADLE)"), "PGW", IF(OR(Q3="FEMALE GENERAL WARD"),"FGW", "NA")))))))

 

[anshikam]

you have missied some paranthesis. i have fixed it check below

=IF(OR(Q3="DELUXE",Q3="DELUXE (CRADLE)", Q3="FOUR SHARING",Q3="PRIVATE ROOM",Q3="TWIN SHARING",Q3="TWIN SHARING AC ROOM",Q3="TWIN SHARING A/C ROOM"), "DELUXE", IF(OR(Q3="GENERAL WARD -1", Q3= "GENERAL WARD -2", Q3="TRIPLE SHARING A/C ROOM"), "COVID", IF(OR(Q3="ICU - 1", Q3="ICU - II", Q3="ICU - III",Q3="Pvt ICU I", Q3="Pvt ICU III"),"ICU", IF(OR(Q3="MATERNITY AND GYNAE WARD",Q3= "MATERNITY PRIVATE ROOM (CRADLE)",Q3="MATERNITY PVT. ROOM", Q3= "MATERNITY TW SH - A/C (CRADEL)",Q3="MATERNITY TWIN SHARING A/C", Q3= "MATERNITY WARD (CRADLE)"), "MATERNITY", IF(OR(Q3="MALE GENERAL WARD"), "MGW", IF(OR(Q3="NICU -1B", Q3="NICU -2B", Q3="PAEDIATRIC GENERAL WARD", Q3="PICU -A", Q3="PICU -B", Q3="PICU -C", Q3="PRIVATE ROOM(CRADLE)", Q3="TWIN SHARING (CRADLE)"), "PGW", IF(OR(Q3="FEMALE GENERAL WARD"),"FGW", "NA")))))))

Hi thanks this does work however not accurately. Some cells match and some cells are 'NA' . The values in these cells are the same. Is there another way of doing the same thing.

 

[jasonb75]

You're better off creating a lookup table than using such a long and messy formula.

Start by listing out all of the possible entries in Q3 in an empty column, then in the next column enter the correct formula result for each entry. Then use VLOOKUP to find the result from that table.

Assuming that you create your list in T1:T20 with the expected results in U1:U20, your formula would be

=IFERROR(VLOOKUP(Q3,$T$1:$U$20,2,0),"NA")

The list can be located anywhere, people often use a hidden sheet for such things. If you are using a data validation dropdown list in Q3 then you could possibly utilise that for the lookup table.

 

[AlanY]

this is a table search alternative that you can check and amend the conditions more easier

data in A1:I7
check cell in A10 and result in B10

Book1.xlsm
	A	B	C	D	E	F	G	H	I
1	DELUXE	DELUXE	DELUXE (CRADLE)	FOUR SHARING	PRIVATE ROOM	TWIN SHARING	TWIN SHARING AC ROOM	TWIN SHARING A/C ROOM
2	COVID	GENERAL WARD -1	GENERAL WARD -2	TRIPLE SHARING A/C ROOM
3	ICU	ICU - 1	ICU - II	ICU - III	Pvt ICU I	Pvt ICU III
4	MATERNITY	MATERNITY AND GYNAE WARD	MATERNITY PRIVATE ROOM (CRADLE)”	“MATERNITY PVT. ROOM	MATERNITY TW SH - A/C (CRADEL)	MATERNITY TWIN SHARING A/C	MATERNITY WARD (CRADLE)
5	MGW	MALE GENERAL WARD
6	PGW	NICU -1B	NICU -2B	PAEDIATRIC GENERAL WARD	PICU -A	PICU -B	PICU -C	PRIVATE ROOM(CRADLE)	TWIN SHARING (CRADLE)
7	FGW	FEMALE GENERAL WARD)
8
9
10	PICU -B	PGW
Sheet2

Cell Formulas
Range	Formula
B10	B10	=IFERROR(INDEX($A$1:$A$7,SUMPRODUCT(--($B$1:$I$7=A10)*ROW($A$1:$A$7))),"N/A")