# 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)