Index Match formula with multiple criteria

mrexcelrc

Board Regular
Joined
Oct 14, 2010
Messages
140
Office Version
  1. 365
Platform
  1. Windows
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!



ABCDEFG
1
DEBIT_ACCTCREDIT_ACCTPOSTING_AMTACCTG_UNITPOSTING_TYPETRADE_IDACCOUNTING_RULE
2
7408094271806392680.942639REVERSAL8361655FXO-MTM_FULL-BUY
32718063740809456946.052639NEW8361655FXO-MTM_FULL-BUY
47408095471807765317.232639NEW8361655FXO-MTM_FULL-SELL
5
6
7
8
9TradeIDGL AccountDebit/Credit IndBalanceAccounting Rule
1083616552718063D56946.05FXO-MTM_FULL-BUY
1183616557408094C56946.05FXO-MTM_FULL-BUY
<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>
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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!

Something is not clear in your logic.
- First, with the examples you gave, it is not known whether to search column A or B for the account.
- Second, your example for the credit account is not correct, because if you search for "FXO-MTM_FULL-SELL", the result is: 4718077


You can comment
 
Upvote 0
Maybe this helps a little.

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:117.86px;" /><col style="width:113.11px;" /><col style="width:109.31px;" /><col style="width:103.6px;" /><col style="width:171.09px;" /><col style="width:76.04px;" /><col style="width:172.99px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >DEBIT_ACCT</td><td >CREDIT_ACCT</td><td >POSTING_AMT</td><td >ACCTG_UNIT</td><td >POSTING_TYPE</td><td >TRADE_ID</td><td >ACCOUNTING_RULE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">7408094</td><td style="text-align:right; ">2718063</td><td style="text-align:right; ">92680.94</td><td style="text-align:right; ">2639</td><td >REVERSAL</td><td style="text-align:right; ">8361655</td><td >FXO-MTM_FULL-BUY</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">2718063</td><td style="text-align:right; ">7408094</td><td style="text-align:right; ">56946.05</td><td style="text-align:right; ">2639</td><td >NEW</td><td style="text-align:right; ">8361655</td><td >FXO-MTM_FULL-BUY</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">7408095</td><td style="text-align:right; ">4718077</td><td style="text-align:right; ">65317.23</td><td style="text-align:right; ">2639</td><td >NEW</td><td style="text-align:right; ">8361655</td><td >FXO-MTM_FULL-SELL</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >TradeID</td><td >GL Account</td><td >Debit/Credit Ind</td><td >Balance</td><td >Accounting Rule</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">8361655</td><td style="text-align:right; ">2718063</td><td >D</td><td style="text-align:right; ">56946.05</td><td >FXO-MTM_FULL-BUY</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">8361655</td><td style="text-align:right; ">4718077</td><td >C</td><td style="text-align:right; ">56946.05</td><td >FXO-MTM_FULL-BUY</td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B11</td><td >=INDEX($A$1:$A$4,SUMPRODUCT(($F$2:$F$4=A11)*($E$2:$E$4="NEW")*($G$2:$G$4="FXO-MTM_FULL-BUY")*(ROW($F$2:$F$4))))</td></tr></table></td></tr></table>
 
Upvote 0
Hi Dante,
This works! Do you have the rest of the formulas on cells c11-c12, d11-d12 and e11-e12?

Thank you so much!
 
Upvote 0
Hi Dante,
Sorry, I want to clarify because it looks like the formula is not picking up the expected value on cell B12. The value should be 7408094 because that is the credit account number of that record. Rows 11 and 12 represent the values of the DEBIT and CREDIT values in row 3. So that is the first condition which is based on "NEW" value in POSTING_TYPE column. The other condition is based on "REVERSAL" value in column POSTING_TYPE using the same logic but looking for "REVERSAL" records.

I hope I clarified it.
Thank you so much again!
 
Upvote 0
Try

Excel Workbook
ABCDEFG
1DEBIT_ACCTCREDIT_ACCTPOSTING_AMTACCTG_UNITPOSTING_TYPETRADE_IDACCOUNTING_RULE
27408094271806392680.942639REVERSAL8361655FXO-MTM_FULL-BUY
32718063740809456946.052639NEW8361655FXO-MTM_FULL-BUY
47408095471807765317.232639NEW8361655FXO-MTM_FULL-SELL
5
6
7
8
9
10TradeIDGL AccountDebit/Credit IndBalanceAccounting Rule
1183616552718063D56946.05FXO-MTM_FULL-BUY
1283616557408094C56946.05FXO-MTM_FULL-BUY
Hoja2
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top