Spreadsheet: 692236d1598205873-if-formulas-with-multiple-criterias-and-vlookup-2020-08-23-oeldere-guigoz-692231d1598201770-if-formulas-with-multiple-criterias-and-vlookup-t
Sheet: Sheet1

Client | Result wanted | [NAME] to find QTY TRANSIT for client 138 & 289 | [NAME] to find QTY TRANSIT for other clients | Status | Invoice | Product | QTY | Vlookup to include for Other clients
138 | 0 |  | IF(AND(NOT(OR(A2=138,A2=289))),IF(OR(LEFT(E2,3)="Rej",LEFT(E2,3)="Shi"),ISNA(VLOOKUP(F2,Sheet2!$A:$C,1,FALSE)"",Sheet1!H2)"",Sheet1!H2) | Shipment confirmed | 1001 | Apple | 2 | IF(ISNA(VLOOKUP(F2,Sheet2!$A:$C,1,FALSE)),"",H2)
138 | 0 |  |  | Shipment confirmed | 1001 | Oranges | 1
101 |  |  |  | Finance Approval | 1002 | Kiwi | 4
101 | 0 |  |  | Shipment confirmed | 1003 | Oranges | 3
314 |  |  |  | Logistic Picking | 1004 | Kiwi | 6
289 |  |  |  | Legalization | 1005 | Bananas | 1
289 |  |  |  | Finance Approval | 1005 | [NAME] | 1
289 | 0 |  |  | Shipment confirmed | 1006 | Peach | 3
289 |  |  |  | Logistic Picking | 1007 | Watermelon | 1
138 | 0 |  |  | Rejected | 1008 | Mango | 2
138 |  |  |  | Finance Approval | 1009 | Bananas | 2
138 |  |  |  | Legalization | 1010 | Apple | 1
138 | 0 |  |  | Shipment confirmed | 1011 | [NAME] | 1
138 | 0 |  |  | Rejected | 1012 | Kiwi | 1
138 |  |  |  | Logistic Picking | 1013 | Kiwi | 1
138 |  |  |  | Finance Approval | 1014 | Oranges | 1
101 | 0 |  |  | Rejected | 1015 | Kiwi | 1
101 |  |  |  | Legalization | 1016 | Bananas | 2
122 |  |  |  | Legalization | 1016 | [NAME] | 8
122 | 8 |  |  | Shipment confirmed | 1017 | Peach | 8
101 |  |  |  | Finance Approval | 1018 | Watermelon | 34
187 | 0 |  |  | Shipment confirmed | 1019 | Mango | 9
187 | 0 |  |  | Shipment confirmed | 1019 | Bananas | 7
300 |  |  |  | Logistic Picking | 1020 | Watermelon | 3
300 |  |  |  | Shipment confirmed | 1021 | Mango | 21
300 |  |  |  | Legalization | 1022 | Bananas | 2
300 |  |  |  | Finance Approval | 1023 | Apple | 5
289 |  |  |  | Legalization | 1024 | [NAME] | 2
289 | 0 |  |  | Shipment confirmed | 1025 | Kiwi | 5
289 |  |  |  | Legalization | 1026 | [NAME] | 8
289 | 0 |  |  | Rejected | 1027 | [NAME] | 3
289 |  |  |  | Logistic Picking | 1028 | Bananas | 8
289 | 2 |  |  | Legalization | 1029 | Pear | 2
323 | 0 |  |  | Rejected | 1030 | Peach | 1
123 | 5 |  |  | Shipment confirmed | 1031 | Oranges | 5
413 |  |  |  | Logistic Picking | 1031 | Watermelon | 3
 |  |  | Hi,
 |  |  | Im trying to find a way to know the quantity in transit of some invoices from a brut data extraction but i have several conditions based on the client code and the status to include to find the correct result which are as follow:
 |  |  | If the client is equal to 138 or 289 AND if the Status (column E) is not Rejected or Shipment confirmed then the result should be the QTY in column H (i have the formula for this scenario in my file column C)
 |  |  | if the status is shipment confirmed then i need to do a vlookup with removing the #N/A of the invoice (column F) on the second sheet called invoice not received
 |  |  | if the invoice is found the result is the QTY in column H if not found then it should be 0,
 |  |  | and lastly if the status is not rejected nor shipment confirmed then the result should be column H
 |  |  | For the point 2, in my file its the formulas in column D that i tried to use but i couldn't manage to make it work, the result should be as in Column B.
 |  |  | And the best would be to combine the point 1 & 2 inside the same cell but im not sure that its possible.
 |  |  | Anyone could help me with this?
 |  |  | Thanks