IFS issues

[beginvbaanalyst]

Hi everyone,

I'm having issues trying to pull information between two sheets in a workbook. Basically I want to check multiple criteria "800 005", "100" in one sheet to see if my new sheet contains the same data, and if it doesn't pull the balance from the old sheet. How would I go about doing this? I'll post two screen shots of small data for an example:
This is last month

This is current month

So it looks like 0000218122-0062 & 000209623-0062 is in my last month but not current month. How do I pull the balance?

 

[Gimics]

Hey there,

You could just use something like an IFERROR or IF formula to lookup if the value is in your current month, then if it fails, pull from the prior month.

It would look something like

=IFERROR(vlookup formula for current month, vlookup formula for prior month)

This way the prior month's lookup only occurs if the current month's formula results in an error.

 

[beginvbaanalyst]

Hey there,

You could just use something like an IFERROR or IF formula to lookup if the value is in your current month, then if it fails, pull from the prior month.

It would look something like

=IFERROR(vlookup formula for current month, vlookup formula for prior month)

This way the prior month's lookup only occurs if the current month's formula results in an error.

I'm trying to find the sum of difference if the 100 type changed to an 99, 98, 97 etc. I'm unsure if this would work as i'm not trying to calculate line by line, these are both tables (should have mentioned that). What formula could you do then? Example:
Previous month has two 100's and now for the current month I just have one 100 and the second 100 is now a 98. I want it to pull the 98 balance when it was a 100. It sounds like I need to concatenate two things together so the formula differentiates between the two.

 

[Gimics]

Could you try providing a real example? Do the two screenshots you shared in your original post have an example in them where the type changed and you want to pull in the prior balance?

I would have to guess, but I think you're trying to lookup the Loan ID to check if the "Type" has changed from this month to last month? And if it has changed, then pull the prior month?

What about the 800 005 types? Do they change in similar way?

 

[awoohaw]

How are you bringing in in the loan numbers that are in question? Are you copy/pasting?

 

[beginvbaanalyst]

Could you try providing a real example? Do the two screenshots you shared in your original post have an example in them where the type changed and you want to pull in the prior balance?

I would have to guess, but I think you're trying to lookup the Loan ID to check if the "Type" has changed from this month to last month? And if it has changed, then pull the prior month?

What about the 800 005 types? Do they change in similar way?

Previous Month

Current Month

This is me putting random information in that's all. If I was tracking Clean type 105 & 99 it would show the same loan ID but the clean type is now 94 & 1000. These both now have different balances too, so I want the sum of balance if the join column from the current month no longer contains text "99" and "105". Might be the best way to explain it. Keep in mind these are both in their own tables.

 

[awoohaw]

can you post an exl2bb add in example please? Or at least a pasted table of the data. Pictures do not help much. Link to xl2bb add is below.

 

[beginvbaanalyst]

can you post an exl2bb add in example please? Or at least a pasted table of the data. Pictures do not help much. Link to xl2bb add is below.

Clean Type	Loan ID	Join	Balance
800008	0000195996-0203	0000195996-0203-800008	$116,000.00
105	0009862337-0203	0009862337-0203-105	$13,000.00
99	0000209623-0205	0000209623-0205-99	$101,000.00

 

[beginvbaanalyst]

Clean Type	Loan ID	Join	Balance
800008	0000195996-0203	0000195996-0203-800008	$116,000.00
105	0009862337-0203	0009862337-0203-105	$13,000.00
99	0000209623-0205	0000209623-0205-99	$101,000.00

Clean Type	Loan ID	Join	Balance
900012	0000195996-0203	0000195996-0203-900012	$57,000.00
94	0009862337-0203	0009862337-0203-94	$52,000.00
1000	0000209623-0205	0000209623-0205-1000	$38,000.00

 

[awoohaw]

@beginvbaanalyst , which of the two data posts is the prior month, and which is the current?