Complicated search row on multiple criteria condition rows and columns

[loongnp]

how do I implement a function formula to search a matching value based on the multiple conditions of rows and columns below inside LibreOffice calc application under the below array table sheet showed?

	A	B	C	D	E	F	G	H
1	Orange	C	0.10	0.20	Y	0.20	0.45
2	Grey	M	0.10	0.15	K	0.10	0.15
3	Lilac	M	0.16	0.30	K	0.16	0.30
4	Blue 544	C	0.25	0.40	K	0.25	0.40
5	Blue 291	C	0.25	0.40	Y	0.10	0.20
6	Dark Blue	C	0.40	0.75	K	0.45	0.75
7	Red	Y	0.60	0.90	M	0.60	0.90
8
9	Auto Density Color	Auto density	Density-C	Density-M	Density-Y	Density-K	Pseudo Color	Type of Color
10	Y	0.36	0.11	0.26	0.36	0.19	#FFEEC6AA
11	M	0.22	0.2	0.22	0.16	0.22	#FFD0C9DC
12	C	0.56	0.56	0.43	0.33	0.5	#FF81A0BA

using density-C ($C$10) column, density-M ($D$10), density-Y ($E$10), and density-K($F$10) to search type of color column ($H$10) from the array table range $A$1:$G$7 (orange, grey…).

I have tried this formula array function below but it is failing, please do assist me.
=TEXTJOIN(";",1,IF( AND ( OR(B1:B7 =“C”, C10 >= C1:C7, C10 <= D1:D7, B1:B7 =“M”, D10 >= C1:C7, D10 <= D1:D7, B1:B7 = “Y”, E10 >= C1:C7, E10 <= D1:D7, B1:B7 = “K”, F10 >= C1:C7, F10 <= D1:D7)
OR(E1:E7 =“C”, C10 >= F1:F7, C10 <= G1:G7, E1:E7 =“M”, D10 >= F1:F7, D10 <= G1:G7, E1:E7 = “Y”, E10 >= F1:F7, E10 <= G1:G7, E1:E7 = “K”, F10 >= F1:F7, F10 <= G1:G7)
) > 2
CYMK DATA RANGE OF VISA SHEET -question.ods (29.2 KB)
, $H10:H11, “”))

As per the attached file for your perusal and reference.

[JohnSUN]

Error Err:509 stands for Missing operator. In this case, Calc did not understand the construction
AND (OR () OR ())
(I didn’t understand it either)

[JohnSUN]

If we follow your logic exactly (as far as I understand it), then the formula should be
{=TEXTJOIN(";";1;IF((($M$40:$M$46="C")*(E2>=$N$40:$N$46)*(E2<=$O$40:$O$46)+($M$40:$M$46="M")*(F2>=$N$40:$N$46)*(F2<=$O$40:$O$46)+($M$40:$M$46="Y")*(G2>=$N$40:$N$46)*(G2<=$O$40:$O$46)+($M$40:$M$46="K")*(H2>=$N$40:$N$46)*(H2<=$O$40:$O$46))*(($P$40:$P$46="C")*(E2>=$Q$40:$Q$46)*(E2<=$R$40:$R$46)+($P$40:$P$46="M")*(F2>=$Q$40:$Q$46)*(F2<=$R$40:$R$46)+($P$40:$P$46="Y")*(G2>=$Q$40:$Q$46)*(G2<=$R$40:$R$46)+($P$40:$P$46="K")*(H2>=$Q$40:$Q$46)*(H2<=$R$40:$R$46));$L$40:$L$46;""))}
Honestly, I do not write such formulas. If the length of the formula is more than 30-45 characters, this is a bad formula! If I have to force the computer to do such calculations, I am wondering how to modify my tables to make Calc’s work easier. Or I write a macro.
CYMK DATA RANGE OF VISA SHEET -answer.ods (31.2 KB)