 Complicated search row on multiple criteria condition rows and columns

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.

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)

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)