Hello Excel wizards!
I'm wondering if anybody could help me write formula that based on table A1:G4 below will:
1. In cell C10, formula that will look for trade 8361655 in the table and if "NEW" under column POSTING_TYPE and "FXO-MTM_FULL-BUY" under column ACCOUNTING_RULE then lookup the corresponding DEBIT_ACCOUNT number which is 2718063
2. In cell D10, using same logic in no.1, formula that will place "D" since 2718063 s in DEBIT_ACCT column for that record.
3. In cell C11 and D11,similar formulas as no. 1 and 2 but the logic "FXO-MTM_FULL-SELL" in column ACCOUNTING_RULE.
Thank you very much for your help!
<colgroup><col width="64" style="width: 48pt;">
<col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;">
<col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;">
<col width="104" style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;">
<col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3401;">
<col width="142" style="width: 107pt; mso-width-source: userset; mso-width-alt: 5193;">
<col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;">
<col width="130" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4754;">
<tbody>
</tbody>
I'm wondering if anybody could help me write formula that based on table A1:G4 below will:
1. In cell C10, formula that will look for trade 8361655 in the table and if "NEW" under column POSTING_TYPE and "FXO-MTM_FULL-BUY" under column ACCOUNTING_RULE then lookup the corresponding DEBIT_ACCOUNT number which is 2718063
2. In cell D10, using same logic in no.1, formula that will place "D" since 2718063 s in DEBIT_ACCT column for that record.
3. In cell C11 and D11,similar formulas as no. 1 and 2 but the logic "FXO-MTM_FULL-SELL" in column ACCOUNTING_RULE.
Thank you very much for your help!
A | B | C | D | E | F | G | |
1 | DEBIT_ACCT | CREDIT_ACCT | POSTING_AMT | ACCTG_UNIT | POSTING_TYPE | TRADE_ID | ACCOUNTING_RULE |
2 | 7408094 | 2718063 | 92680.94 | 2639 | REVERSAL | 8361655 | FXO-MTM_FULL-BUY |
3 | 2718063 | 7408094 | 56946.05 | 2639 | NEW | 8361655 | FXO-MTM_FULL-BUY |
4 | 7408095 | 4718077 | 65317.23 | 2639 | NEW | 8361655 | FXO-MTM_FULL-SELL |
5 | |||||||
6 | |||||||
7 | |||||||
8 | |||||||
9 | TradeID | GL Account | Debit/Credit Ind | Balance | Accounting Rule | ||
10 | 8361655 | 2718063 | D | 56946.05 | FXO-MTM_FULL-BUY | ||
11 | 8361655 | 7408094 | C | 56946.05 | FXO-MTM_FULL-BUY |