Index and Multiple Match function

RAJESH1960

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
Hello, I need help in rectifying this formula.

If the values in columns H,J and M are matching with columns A,C and F, then column H should dis[lay the same name as in column B, else display No Match.

The goal is to get the result in column H.



=INDEX($B$3:$B$16,MATCH($H3&$J3&$M3,$A$3:$A$16&$C$3:$C$16&$F$3:$F$16,0))
 

Attachments

  • 3Untitled.png
    3Untitled.png
    35.3 KB · Views: 13

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
Can you give us the sample data in a form that we can use to test? -> XL2BB
 
Upvote 0
=INDEX($B$3:$B$16,MATCH(1,INDEX(($A$3:$A$16=H3)*($C$3:$C$16=I3)*($F$3:$F$16=M3),0,1)))
I tried this too but something must be wrong with the formula. Can you pinpoint the mistake?
 
Upvote 0
RAJESH1960 said:
Can you pinpoint the mistake?
Click to expand...
It would be much easier with some sample data that we can use. We cannot copy/paste from the images you are providing, hence my suggestion to use XL2BB that we can copy from.
 
Upvote 0
Peter_SSs said:
It would be much easier with some sample data that we can use. We cannot copy/paste from the images you are providing, hence my suggestion to use XL2BB that we can copy from.
Click to expand...
Yeah I understand. I had done it once long back but I don't remember now. So, I am trying and looking at how to use XL2BB and upload it.
 
Upvote 0
RAJESH1960 said:
=INDEX($B$3:$B$16,MATCH(1,INDEX(($A$3:$A$16=H3)*($C$3:$C$16=I3)*($F$3:$F$16=M3),0,1)))
I tried this too but something must be wrong with the formula. Can you pinpoint the mistake?
Click to expand...
Problem Solved.
 
Upvote 0
RAJESH1960 said:
Problem Solved.
Click to expand...
Problem Persists. I need help again to overcome the problem.
Repeat matching index, match.xlsx
ABCDEFGHIJKLMNO
1OWN ACCOUNTBANK STATEMENT
2DateLedgerNameVch TypeDebitCreditAmountDateLedgerNameVch TypeDebitCreditAmount
301-07-2020DecemberReceipt5,000.005,000.0001-07-2020DecemberReceipt5,000.005,000.00
401-07-2020FebruaryReceipt200.00200.0001-07-2020FebruaryReceipt200.00200.00
501-07-2020FridayPayment5,000.005,000.0001-07-2020FridayPayment5,000.005,000.00
601-07-2020AprilReceipt5,000.005,000.0001-07-2020DecemberReceipt5,000.005,000.00
701-07-2020Bank of BarodaContra5,000.005,000.0001-07-2020Bank of BarodaContra5,000.005,000.00
801-07-2020FridayPayment2,500.002,500.0001-07-2020FridayPayment5,000.005,000.00
901-07-2020SaturdayPayment2,500.002,500.0001-07-2020Bank of BarodaContra5,000.005,000.00
1001-07-2020Kotak Mahindra BankContra5,000.005,000.0002-07-2020ThursdayPayment5,000.005,000.00
1102-07-2020ThursdayPayment5,000.005,000.0002-07-2020ThursdayPayment5,000.005,000.00
1202-07-2020Bank ChargesPayment5,000.005,000.0002-07-2020ThursdayPayment5,000.005,000.00
1302-07-2020Bank ChargesPayment5,000.005,000.0002-07-2020DecemberReceipt5,000.005,000.00
1402-07-2020OctoberReceipt2,500.002,500.0002-07-2020DecemberReceipt5,000.005,000.00
1502-07-2020OctoberReceipt2,500.002,500.0002-07-2020DecemberReceipt5,000.005,000.00
1602-07-2020DecemberReceipt5,000.005,000.0002-07-2020DecemberReceipt5,000.005,000.00
1702-07-2020JulyReceipt5,000.005,000.00
1802-07-2020AugustReceipt5,000.005,000.00
19=IFERROR(INDEX($B$3:$B$18,MATCH(1,INDEX(($A$3:$A$18=F19)*($C$3:$C$18=H19)*($F$3:$F$18=K19),0,1),0)),"Suspense")
2035,000.0030,200.0035,000.0030,200.00
21
22When the amounts are similar on the same date, it is taking the first name from the Ledgername . Then again next entry also it is taking the same name. I want it to take the next name if the first name is already taken. The cells marked Yellow in I column all are wrong answers. I need help to correct the error in the formula.
FB Query
Cell Formulas
RangeFormula
I3:I16I3=IFERROR(INDEX($B$3:$B$18,MATCH(1,INDEX(($A$3:$A$18=H3)*($C$3:$C$18=J3)*($F$3:$F$18=M3),0,1),0)),"Suspense")
F3:F18,M3:M16M3=K3+L3
K20:L20,D20:E20D20=SUM(D3:D19)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G19Cell Valuecontains "No Match Found"textNO
I3:I16Cell Valuecontains "No Match Found"textNO
 
Upvote 0
You say that the yellow cells in column J are all incorrect & I understand your reasoning. My question is why is cell I8 not also yellow?
That row is the second 'Bank Statement' row with 01-07-2020/Payment/5000 when there is only one such row in the 'Own Account' section?
So ..
- If I8 is correct showing "Friday", can you give the reasoning behind that?
- If I8 should not show "Friday", what should it show?

There is a similar situation further down where there are 3 rows on the left but 4 rows the same on the right.

RAJESH1960.xlsm
ABCDEFGHIJKLM
1OWN ACCOUNTBANK STATEMENT
2DateLedgerNameVch TypeDebitCreditAmountDateLedgerNameVch TypeDebitCreditAmount
301-07-2020DecemberReceipt5000500001-07-2020DecemberReceipt50005000
401-07-2020FebruaryReceipt20020001-07-2020FebruaryReceipt200200
501-07-2020FridayPayment5000500001-07-2020FridayPayment50005000
601-07-2020AprilReceipt5000500001-07-2020DecemberReceipt50005000
701-07-2020Bank of BarodaContra5000500001-07-2020Bank of BarodaContra50005000
801-07-2020FridayPayment2500250001-07-2020FridayPayment50005000
901-07-2020SaturdayPayment2500250001-07-2020Bank of BarodaContra50005000
1001-07-2020Kotak Mahindra BankContra5000500002-07-2020ThursdayPayment50005000
1102-07-2020ThursdayPayment5000500002-07-2020ThursdayPayment50005000
1202-07-2020Bank ChargesPayment5000500002-07-2020ThursdayPayment50005000
1302-07-2020Bank ChargesPayment5000500002-07-2020DecemberReceipt50005000
1402-07-2020OctoberReceipt2500250002-07-2020DecemberReceipt50005000
1502-07-2020OctoberReceipt2500250002-07-2020DecemberReceipt50005000
1602-07-2020DecemberReceipt5000500002-07-2020DecemberReceipt50005000
1702-07-2020JulyReceipt50005000
1802-07-2020AugustReceipt50005000
Sheet2



In case the yellow cells in my sheet above are incorrect too, then you could check to see if this does what you want.

RAJESH1960.xlsm
ABCDEFGHIJKLM
1OWN ACCOUNTBANK STATEMENT
2DateLedgerNameVch TypeDebitCreditAmountDateLedgerNameVch TypeDebitCreditAmount
301-07-2020DecemberReceipt5000500001-07-2020DecemberReceipt50005000
401-07-2020FebruaryReceipt20020001-07-2020FebruaryReceipt200200
501-07-2020FridayPayment5000500001-07-2020FridayPayment50005000
601-07-2020AprilReceipt5000500001-07-2020AprilReceipt50005000
701-07-2020Bank of BarodaContra5000500001-07-2020Bank of BarodaContra50005000
801-07-2020FridayPayment2500250001-07-2020 Payment50005000
901-07-2020SaturdayPayment2500250001-07-2020Kotak Mahindra BankContra50005000
1001-07-2020Kotak Mahindra BankContra5000500002-07-2020ThursdayPayment50005000
1102-07-2020ThursdayPayment5000500002-07-2020Bank ChargesPayment50005000
1202-07-2020Bank ChargesPayment5000500002-07-2020Bank ChargesPayment50005000
1302-07-2020Bank ChargesPayment5000500002-07-2020DecemberReceipt50005000
1402-07-2020OctoberReceipt2500250002-07-2020JulyReceipt50005000
1502-07-2020OctoberReceipt2500250002-07-2020AugustReceipt50005000
1602-07-2020DecemberReceipt5000500002-07-2020 Receipt50005000
1702-07-2020JulyReceipt50005000
1802-07-2020AugustReceipt50005000
Sheet1
Cell Formulas
RangeFormula
I3:I16I3=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$3:B$18)/((A$3:A$18=H3)*(C$3:C$18=J3)*(F$3:F$18=M3)),COUNTIFS(H$2:H2,H3,J$2:J2,J3,M$2:M2,M3)+1)),"")
M3:M16,F3:F18M3=K3+L3
 
Upvote 0
Solution
Peter_SSs said:
You say that the yellow cells in column J are all incorrect & I understand your reasoning. My question is why is cell I8 not also yellow?
That row is the second 'Bank Statement' row with 01-07-2020/Payment/5000 when there is only one such row in the 'Own Account' section?
So ..
- If I8 is correct showing "Friday", can you give the reasoning behind that?
- If I8 should not show "Friday", what should it show?

There is a similar situation further down where there are 3 rows on the left but 4 rows the same on the right.

RAJESH1960.xlsm
ABCDEFGHIJKLM
1OWN ACCOUNTBANK STATEMENT
2DateLedgerNameVch TypeDebitCreditAmountDateLedgerNameVch TypeDebitCreditAmount
301-07-2020DecemberReceipt5000500001-07-2020DecemberReceipt50005000
401-07-2020FebruaryReceipt20020001-07-2020FebruaryReceipt200200
501-07-2020FridayPayment5000500001-07-2020FridayPayment50005000
601-07-2020AprilReceipt5000500001-07-2020DecemberReceipt50005000
701-07-2020Bank of BarodaContra5000500001-07-2020Bank of BarodaContra50005000
801-07-2020FridayPayment2500250001-07-2020FridayPayment50005000
901-07-2020SaturdayPayment2500250001-07-2020Bank of BarodaContra50005000
1001-07-2020Kotak Mahindra BankContra5000500002-07-2020ThursdayPayment50005000
1102-07-2020ThursdayPayment5000500002-07-2020ThursdayPayment50005000
1202-07-2020Bank ChargesPayment5000500002-07-2020ThursdayPayment50005000
1302-07-2020Bank ChargesPayment5000500002-07-2020DecemberReceipt50005000
1402-07-2020OctoberReceipt2500250002-07-2020DecemberReceipt50005000
1502-07-2020OctoberReceipt2500250002-07-2020DecemberReceipt50005000
1602-07-2020DecemberReceipt5000500002-07-2020DecemberReceipt50005000
1702-07-2020JulyReceipt50005000
1802-07-2020AugustReceipt50005000
Sheet2



In case the yellow cells in my sheet above are incorrect too, then you could check to see if this does what you want.

RAJESH1960.xlsm
ABCDEFGHIJKLM
1OWN ACCOUNTBANK STATEMENT
2DateLedgerNameVch TypeDebitCreditAmountDateLedgerNameVch TypeDebitCreditAmount
301-07-2020DecemberReceipt5000500001-07-2020DecemberReceipt50005000
401-07-2020FebruaryReceipt20020001-07-2020FebruaryReceipt200200
501-07-2020FridayPayment5000500001-07-2020FridayPayment50005000
601-07-2020AprilReceipt5000500001-07-2020AprilReceipt50005000
701-07-2020Bank of BarodaContra5000500001-07-2020Bank of BarodaContra50005000
801-07-2020FridayPayment2500250001-07-2020 Payment50005000
901-07-2020SaturdayPayment2500250001-07-2020Kotak Mahindra BankContra50005000
1001-07-2020Kotak Mahindra BankContra5000500002-07-2020ThursdayPayment50005000
1102-07-2020ThursdayPayment5000500002-07-2020Bank ChargesPayment50005000
1202-07-2020Bank ChargesPayment5000500002-07-2020Bank ChargesPayment50005000
1302-07-2020Bank ChargesPayment5000500002-07-2020DecemberReceipt50005000
1402-07-2020OctoberReceipt2500250002-07-2020JulyReceipt50005000
1502-07-2020OctoberReceipt2500250002-07-2020AugustReceipt50005000
1602-07-2020DecemberReceipt5000500002-07-2020 Receipt50005000
1702-07-2020JulyReceipt50005000
1802-07-2020AugustReceipt50005000
Sheet1
Cell Formulas
RangeFormula
I3:I16I3=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$3:B$18)/((A$3:A$18=H3)*(C$3:C$18=J3)*(F$3:F$18=M3)),COUNTIFS(H$2:H2,H3,J$2:J2,J3,M$2:M2,M3)+1)),"")
M3:M16,F3:F18M3=K3+L3
Click to expand...
Peter, You are right. I marked just a few cells to mark the error and missed to mark the I8 cell. But you anyhow got it right. I wanted to show Suspense if it didn't match the criteria and I changed that in your formula. As this is a reconciliation of the bank statement, all the amounts may not match. So, I can post the non matching cells with suspense.
Anyways, you gave me the right answer. Thanks a lot.?
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
MATCH function
2
Replies
14
Views
146
Fluff
Fluff
DNapoli
  • Solved
Index and Match Excel Question.
2 3
Replies
24
Views
480
Peter_SSs
Peter_SSs
T
  • Question
Pull data from multiple sheets that match varying criteria
Replies
0
Views
35
T1991
T
J
  • Question
Conditional Formatting.. The bane of my Excel Existence
Replies
2
Views
42
RoryA
RoryA
N
  • Question
Match and lookup combined
Replies
4
Views
191
Glenn Kennedy
G

Forum statistics

Threads
1,215,170
Messages
6,123,422
Members
449,099
Latest member
COOT

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