If Multiple Conditions

[beginvbaanalyst]

Hi Everyone,

Struggling with three different scenarios; scenario one: if Column Row says "New" return "new", scenario two: if scenario one is true, but date is less than 2/1/2023 return "transfer", scenario three: if scenario one and two are true, if balance is greater than (>) past month balance return "balance went up"))).

My formula is currently this:
=IF([@UpdatedChange]="New",+IF([@OpenDate1]<DATE(2023,2,1),"transfer",+IF([@Balance]>VLOOKUP([@LoanID],'Jan23'!B:H,7,0),"balance went up")))

My returns are #N/A, False, and transfer.
What in my formula is causing this?

 

[Alex Blakenburg]

I suspect you can condense this further but try this:
PS: You will generally find you get a response is you provide some sample data for us to work with.

20230415 Nested If Statement beginvbaanalyst.xlsx
	A	B	C	D	E	F	G	H
1
2
3		Col1	UpdatedChange	OpenDate1	Balance	LoanID	Formula
4		Apple	New	30/12/2022			transfer
5		Pear	Old	30/12/2022			Not New
6		Orange	New	15/04/2023			Not Found
7		Kiwi					Not New
8
Sheet1

Cell Formulas
Range	Formula
G4:G7	G4	=IF([@UpdatedChange]="New", IF([@OpenDate1]<DATE(2023,2,1), "transfer", IFERROR( IF([@Balance]>VLOOKUP([@LoanID],'Jan23'!B:H,7,0), "balance went up", ""), "Not Found")), "Not New")

 

[beginvbaanalyst]

I suspect you can condense this further but try this:
PS: You will generally find you get a response is you provide some sample data for us to work with.

20230415 Nested If Statement beginvbaanalyst.xlsx
	A	B	C	D	E	F	G	H
1
2
3		Col1	UpdatedChange	OpenDate1	Balance	LoanID	Formula
4		Apple	New	30/12/2022			transfer
5		Pear	Old	30/12/2022			Not New
6		Orange	New	15/04/2023			Not Found
7		Kiwi					Not New
8
Sheet1

Cell Formulas
Range	Formula
G4:G7	G4	=IF([@UpdatedChange]="New", IF([@OpenDate1]<DATE(2023,2,1), "transfer", IFERROR( IF([@Balance]>VLOOKUP([@LoanID],'Jan23'!B:H,7,0), "balance went up", ""), "Not Found")), "Not New")

Thank you for the help, this appears to work but I realize for the balance I need to vlookup the first part as well to make sure Im comparing exact data to exact data.
IFERROR(IF([@[Original Limit]]<VLOOKUP([@[Loan ID]],'Feb23'!B:H,6,0),"balance went up","")
I'm trying to adjust this to do a vlookup < vlookup but for some reason it's breaking, I can't get the balance went up to show up at all. My current formula is this: IFERROR(IF(VLOOKUP([@[Loan ID]],B:G,6,0)<VLOOKUP([@[Loan ID]],'Feb23'!B:H,6,0),"balance went up","")

 

[Alex Blakenburg]

You might need to provide some sample data (ideally using XL2BB) with expected results.
I don't understand what you are looking up with this part

VLOOKUP([@[Loan ID]],B:G,6,0)

It seems to be looking up the current row ID against the table that row ID is in which doesn't make sesnse to me.