VLOOKUP and Array Formula With Match Every Columns

Question

I have database in Google Sheets, Let's say the named sheet is Chemical. In Sheet Chemical, I have data in the range Chemical!A:E as pictured below:

Then i have database in the range Chemical!G1:I10 as pictured below:

In column C, D, and E, I would like to classify using VLOOKUP based on categories from columns H and I. In the example, if the value of column A (Product Name) matches column G, then columns C, D, E will be filled based on the match between columns H and I. Example cell A7, Oxteril 250 LRA, if viewed database, Oxteril 250 LRA has Parameter 1: Acid and Parameter 2: H2O2. So, columns D and E are true because they match. Finally, the output in columns D and E is 4 each because Sampling Qty is 4.

Below is an example of my expected output based on the sample data above.

Before that, i have created formula and it worked (needs dragging formula). Following my formula in column C, D, and E:

Column C:

=IF(AND(VLOOKUP(A2; $G$1:$I$11;  2; FALSE) = "Base"; OR(VLOOKUP(A2; $G$1:$I$11; 3; FALSE) = "-"; VLOOKUP(A2; $G$1:$I$11; 3; FALSE) <> "-")); B2; if(AND(VLOOKUP(A2; $G$1:$I$11;  2; FALSE) <> "Base"; VLOOKUP(A2; $G$1:$I$11; 3; FALSE) = "Base"); B2; ""))

Column D:

=IF(AND(VLOOKUP(A2; $G$1:$I$11;  2; FALSE) = "Acid"; OR(VLOOKUP(A2; $G$1:$I$11; 3; FALSE) = "-"; VLOOKUP(A2; $G$1:$I$11; 3; FALSE) <> "-")); B2; if(AND(VLOOKUP(A2; $G$1:$I$11;  2; FALSE) <> "Acid"; VLOOKUP(A2; $G$1:$I$11; 3; FALSE) = "Acid"); B2; ""))

Column E:

=IF(AND(VLOOKUP(A2; $G$1:$I$11;  2; FALSE) = "H2O2"; OR(VLOOKUP(A2; $G$1:$I$11; 3; FALSE) = "-"; VLOOKUP(A2; $G$1:$I$11; 3; FALSE) <> "-")); B2; if(AND(VLOOKUP(A2; $G$1:$I$11;  2; FALSE) <> "H2O2"; VLOOKUP(A2; $G$1:$I$11; 3; FALSE) = "H2O2"); B2; ""))

My goals is i would like to use array formula without dragging. Before that, i have created formula using array formula but it not worked. As example following my formula in column C:

=ArrayFormula(IF(AND(VLOOKUP(A2:A; $L$1:$N$11;  2; FALSE) = "Base"; OR(VLOOKUP(A2:A; $L$1:$N$11; 3; FALSE) = "-"; VLOOKUP(A2:A; $L$1:$N$11; 3; FALSE) <> "-")); B2:B; if(AND(VLOOKUP(A2:A; $L$1:$N$11;  2; FALSE) <> "Base"; VLOOKUP(A2:A; $L$1:$N$11; 3; FALSE) = "Base"); B2:B; "")))

Please advise me how I can fix the formula to correct this sheet especially for column C, D, and E. Thankyou

Answer 1

You may try:

=map(A2:A;B2:B;lambda(Σ;Λ;if(or(Σ="";Λ="");;index(if(ifna(xmatch(substitute(C1:E1;"Count ";);xlookup(Σ;G:G;H:I;)));Λ;)))))