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
