IF formula question

[beginvbaanalyst]

New question for everyone, I'm trying to sum the prior month balance of all loan ID that are no longer in my current month as it was paid off. I can use this formula but adjust some values right? For some reason I get it to lookup all balances.

=IF(Table2[@[Loan ID]]<>[@[Loan ID]],INDEX(Table2[Balance],MATCH([@[Loan ID]],Table2[Loan ID],0)),"-")

^Current formula that pulls all balance.
Basically in my head I have if the Jan20 (table 2) loan id doesn't match my current month Feb20 (Table 1) loan ID,return the sum of balance for all loans no longer in Feb20. Is it possible to just do one formula to auto calucate all? I would need to add specific types to this. Example "800", "208", "301", I think it's pulling the loan ID balance from all existing loan ids from Feb20 if it was still in Jan20. Maybe I need to switch what my lookup value is?

After thinking more about it, just because the loan no longer exists, the Borrower could have refinanced and it's no new money to us. So it would need to also find any new opened loans in the current month (Table 1) greater than 2/1/20 and then subtract the new loan from old loan for a true value. I have to calculate the total balance as of previous month for specific loan types that paid off in the current month.

 

[ACommandLineKindaGuy]

I don't understand what you're saying. I don't understand the "Table2[@[LoanID]]" you are using as well as all the other terminology. Why don't you post a snippet so we can see the issue

 

[snjpverma]

Could you post a small sample of your data and the output you desire?

Untested:

=SUMIFS(Table2[Balance],Table2[Loan Type],"=800",Table2[Loan Date],"<2/1/2020",ISERROR(MATCH(Table2[Loan ID],Table1[Loan ID],0)))

 

[beginvbaanalyst]

Could you post a small sample of your data and the output you desire?

Untested:

=SUMIFS(Table2[Balance],Table2[Loan Type],"=800",Table2[Loan Date],"<2/1/2020",ISERROR(MATCH(Table2[Loan ID],Table1[Loan ID],0)))

I will give this a go!