If and function with three conditions

[beginvbaanalyst]

Hi Everyone,

I'm trying to add an additional condition that doesn't seem to work and I can't figure out why. The new condition is looking at another column for the word "yes".
My current formula is following:

=IF(AND(COUNTIF(_AG22[Loan ID],[@[Loan ID]]),_AG22[Open Date1]<="8/01/2022"),"-",VLOOKUP([@[Loan ID]],'Jul22'!B:H,7,0))

_AG22 - Table
Loan ID - Column
Open Date1 - Column
Jul22 - Table
If true then add dash, if false then vlookup the loan id for my current table to seperate table to pull info.

My new formula that doesn't work is following:
=IF(AND(COUNTIF(_AG22[Loan ID],[@[Loan ID]]),_AG22[Open Date1]<="8/01/2022",_AG22[Internal Refi]="yes"),"-",VLOOKUP([@[Loan ID]],'Jul22'!B:H,7,0))
Excel accepts the formula but doesn't seem to work, can't figure out why.
Here is my table.
What do you think could be my issue?

Type

Loan ID

Name

Join

Open Date

Open Date1

Original Limit

Balance

Unadvanced

Risk Rate

Change V2

Updated Change

Internal Refi

Payoff

1

212

N/A

N/A

N/A

44784

$10,000​

$3,250

$0.00

-

New

New

yes

-

 

[beginvbaanalyst]

Bump.
After closer inspection it actually is working but instead of referencing the ID from my current table and comparing to AG22 ID still, it's looking at AG22 row 1, row 2, row 3 etc... where actual answer might be in row 203. What's the best way to tackle this?

 

[awoohaw]

=IF(
AND(
COUNTIF(_AG22[Loan ID],[@[Loan ID]]), =======-And Criteria #1, there is no equation, any number other than zero will mean this is TRUE
_AG22[Open Date1]<="8/01/2022", ===========And Criteria #2, Checking a date, And Criteria #3, Checking for "Yes".
_AG22[Internal Refi]="yes"), ============= Is this where the IF Criteria Ends?
"-",==============================this is the result if TRUE
VLOOKUP([@[Loan ID]],'Jul22'!B:H,7,0)=======This is the result if false
)

please post all the data in your table using the xl2bb add in so we can debug with your actual data. (Link is below)

 

[awoohaw]

=IF(
AND(
COUNTIF(_AG22[Loan ID],[@[Loan ID]]), =======-And Criteria #1, there is no equation, any number other than zero will mean this is TRUE
_AG22[Open Date1]<="8/01/2022", ===========And Criteria #2, Checking a date, And Criteria #3, Checking for "Yes".
_AG22[Internal Refi]="yes"), ============= Is this where the IF Criteria Ends?
"-",==============================this is the result if TRUE
VLOOKUP([@[Loan ID]],'Jul22'!B:H,7,0)=======This is the result if false
)

please post all the data in your table using the xl2bb add in so we can debug with your actual data. (Link is below)

Don't post all, but a representative sample (also sanitized for security) so that we have enough to work with.

 

[Fluff]

Is this what you want

=IF(COUNTIF(_AG22[Loan ID],[@[Loan ID]],_AG22[Open Date1],"<=8/01/2022",_AG22[Internal Refi],"yes"),"-",VLOOKUP([@[Loan ID]],'Jul22'!B:H,7,0))

 

[beginvbaanalyst]

Is this what you want

=IF(COUNTIF(_AG22[Loan ID],[@[Loan ID]],_AG22[Open Date1],"<=8/01/2022",_AG22[Internal Refi],"yes"),"-",VLOOKUP([@[Loan ID]],'Jul22'!B:H,7,0))

I think so but excel is saying I've entered too many arguments for this function.

 

[Fluff]

Oops it should be COUNTIFS not COUNTIF

 

[beginvbaanalyst]

Oops it should be COUNTIFS not COUNTIF

oh that's on me, it looks like this is pulling data but not the correct data. I don't think its using yes and no correctly. It's now pulling every balance but we're on the right track. The original formula works perfectly, all i'm trying to do is add a third criteria and it still utilize the Load ID as the search between the tables. Maybe that's where it breaks for the yes and no? It's looking at the entire column instead of exact match for July22 ID (if in row 35) and AG22 ID (if in row 104).

 

[Fluff]

In that case can you post some sample data along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[beginvbaanalyst]

In that case can you post some sample data along with expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

On my work computer, when I am home later today I will submit with XL2BB. I appreciate the guidance. Will continue to test.