Hello all,
I am doing this project at work and I have to match up different amounts. I need to match up amounts from sheet 1 to amounts with sheet 2. The only problem is that if its a debit on sheet 2 it was to match with the number 40 on sheet 1, and if its a credit on sheet 2 then it has to match number 50 on sheet 1. I have attached a sample workbook. The numbers are all fake but this is exactly how my workbook is set up. If you could please help me out that would be great. Thank you.
Is date to be checked too?
If so:
=INDEX(Sheet1!$D$2:$D$205,MATCH(1,INDEX((Sheet1!$A$2:$A$205=B2)*(Sheet1!$B$2:$B$205=D2)*(Sheet1!$C$2:$C$205=IF(LEFT(F2,2)="DR",40,50)),0),0))
if not:
=INDEX(Sheet1!$D$2:$D$205,MATCH(1,INDEX((Sheet1!$B$2:$B$205=D2)*(Sheet1!$C$2:$C$205=IF(LEFT(F2,2)= "DR",40,50)),0),0))
copied down
Last edited by NBVC; 06-01-2012 at 03:42 PM. Reason: removed space in formula left by forum
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
How about
=SUMIFS(Sheet1!D:D,Sheet1!A:A,B2,Sheet1!B:B,D1,Sheet1!C:C,IF(LEFT(F2,1)="D",40,50))
choices, choices, too many choices...
I wasn't actually sure if they were looking for the Amount or something else, so figured Index/match would work with either... I knew somebody would come around with that in the end anyway.... it's always too tempting.
I'm easily tempted.
Good Morning, I tried the formula but when i enter it all i get is a 0
The 232332 in column B of Sheet1 is not the same as 2323232 in D2 of Sheet2, and if you are checking for dates also, there are no matches in Sheet1... after you fix column B of sheet1, and copy down formula in Sheet2, you will see some non-zero results.
I just need to match the amounts I don't need to worry about the accouts. I just need to worry about column C and D in sheet 1 and E and F in sheet 2. If the amount has a label with Dr in sheet 2 it has to match the amount and 40 in sheet 1, if a Cr in sheet 2 it has to match the amount and 50 in sheet 1. I hope that makes sense. I can try to explain it a little better
When you say, match the amount, do you mean that the actual value has to match column D in Sheet1? Are you just trying to get a True/False result based on an exact match of the Post Key and Amount? Or are you trying to sum the amounts in column D of sheet1 for a match in column C?
I just need an exact match. But the only problem is that if its a Dr it has to match the amount and 40 in column C which is the post key. and if its a Cr it has to match the exact amount and a 50 in column C. I'm basically doing a cash reconciliation. I need to know if the items match sheet 2. Hopefully that helps more.
Try:
=COUNTIFS(Sheet1!C:C,IF(LEFT(F2,2)="DR",40,50),Sheet1!D:D,E2)>0
this gives TRUE if match found and FALSE if not...
Am i to put this formula in sheet 1 or sheet 2?
Sheet2, G2 and copied down
Awesome it seems to work. Can i put the formula in another column or does it have to be G2, and also is their anyway you can give me a formula which i can put in sheet 1 which can tell me basically the same thing that is in sheet 2 so this way i can easily match them up between sheets
No, it doesn't have to be in G2... anywhere in Row 2
In Shee1, try:
=COUNTIFS(Sheet2!F:F,IF(C2=40,"DR*","CR*"),Sheet2!E:E,D2)>0
copied down
Okay so the formula for sheet 2 is showing me amounts that are true which shouldn't be. It has the correct amount but it showing me true when its a Dr and it should match post key number 40 but its matching 50. Anyway that can be fixed?
Never mind you did wonders it works!!! thank you so much for your help I really appreciate it. If I have anymore trouble with the formula is it okay if I ask you questions or is their a rule against that in the forum?
As long as the questions are related to this specific issue, you can post again here. If they are new questions on new problems, start new threads to keep topics separate.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks