Index Match formula with multiple criteria

M

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!



[TABLE="width: 589"]
<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>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"]A[/TD]
[TD="width: 82, bgcolor: transparent"]B[/TD]
[TD="width: 104, bgcolor: transparent"]C[/TD]
[TD="width: 93, bgcolor: transparent"]D[/TD]
[TD="width: 142, bgcolor: transparent"]E[/TD]
[TD="width: 86, bgcolor: transparent"]F[/TD]
[TD="width: 130, bgcolor: transparent"]G[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="width: 81, bgcolor: transparent"]DEBIT_ACCT[/TD]
[TD="width: 82, bgcolor: transparent"]CREDIT_ACCT[/TD]
[TD="width: 104, bgcolor: transparent"]POSTING_AMT[/TD]
[TD="width: 93, bgcolor: transparent"]ACCTG_UNIT[/TD]
[TD="width: 142, bgcolor: transparent"]POSTING_TYPE[/TD]
[TD="width: 86, bgcolor: transparent"]TRADE_ID[/TD]
[TD="width: 130, bgcolor: transparent"]ACCOUNTING_RULE[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[TD="width: 81, bgcolor: transparent, align: right"]7408094[/TD]
[TD="width: 82, bgcolor: transparent, align: right"]2718063[/TD]
[TD="width: 104, bgcolor: transparent, align: right"]92680.94[/TD]
[TD="width: 93, bgcolor: transparent, align: right"]2639[/TD]
[TD="width: 142, bgcolor: transparent"]REVERSAL[/TD]
[TD="width: 86, bgcolor: transparent, align: right"]8361655[/TD]
[TD="width: 130, bgcolor: transparent"]FXO-MTM_FULL-BUY[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="width: 81, bgcolor: transparent, align: right"]2718063[/TD]
[TD="width: 82, bgcolor: transparent, align: right"]7408094[/TD]
[TD="width: 104, bgcolor: transparent, align: right"]56946.05[/TD]
[TD="width: 93, bgcolor: transparent, align: right"]2639[/TD]
[TD="width: 142, bgcolor: transparent"]NEW[/TD]
[TD="width: 86, bgcolor: transparent, align: right"]8361655[/TD]
[TD="width: 130, bgcolor: transparent"]FXO-MTM_FULL-BUY[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]7408095[/TD]
[TD="bgcolor: transparent, align: right"]4718077[/TD]
[TD="bgcolor: transparent, align: right"]65317.23[/TD]
[TD="width: 93, bgcolor: transparent, align: right"]2639[/TD]
[TD="width: 142, bgcolor: transparent"]NEW[/TD]
[TD="width: 86, bgcolor: transparent, align: right"]8361655[/TD]
[TD="width: 130, bgcolor: transparent"]FXO-MTM_FULL-SELL[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="width: 93, bgcolor: transparent"] [/TD]
[TD="width: 142, bgcolor: transparent"] [/TD]
[TD="width: 86, bgcolor: transparent"] [/TD]
[TD="width: 130, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent"]TradeID[/TD]
[TD="bgcolor: transparent"]GL Account[/TD]
[TD="bgcolor: transparent"]Debit/Credit Ind[/TD]
[TD="bgcolor: transparent"]Balance[/TD]
[TD="bgcolor: transparent"]Accounting Rule[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="width: 81, bgcolor: transparent, align: right"]8361655[/TD]
[TD="width: 82, bgcolor: transparent, align: right"]2718063[/TD]
[TD="bgcolor: transparent"]D[/TD]
[TD="width: 93, bgcolor: transparent, align: right"]56946.05[/TD]
[TD="width: 142, bgcolor: transparent"]FXO-MTM_FULL-BUY[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="width: 81, bgcolor: transparent, align: right"]8361655[/TD]
[TD="bgcolor: transparent, align: right"]7408094[/TD]
[TD="bgcolor: transparent"]C[/TD]
[TD="width: 93, bgcolor: transparent, align: right"]56946.05[/TD]
[TD="width: 142, bgcolor: transparent"]FXO-MTM_FULL-BUY[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
mrexcelrc said:
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!
Click to expand...

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
You must log in or register to reply here.

Similar threads

G
  • Question Question
Conditional formating where there are 2 values in a cell
Replies
3
Views
1K
Godders199
G
H
  • Question Question
Macros - Auto Copy paste row when cell value changes
Replies
7
Views
6K
Fluff
Fluff
S
  • Solved
Tranpose with Editing
Replies
7
Views
1K
Rick Rothstein
Rick Rothstein
B
  • Question Question
Matching Number To Number In text
2 3
Replies
23
Views
2K
Twollaston
T
S
  • Question Question
Conditional Formating Required for changing Dates based on 2 different cell dates
Replies
1
Views
792
Toadstool
Toadstool

Forum statistics

Threads
1,223,896
Messages
6,175,259
Members
452,626
Latest member
huntinghunter

Share this page

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