I am trying to write a formula for Is Vendor to be used when appending the table into the Cost Source Master table. There are two conditions that need to be met:

1. The vendor type, as referenced in another table, needs to be "VENDOR".
2. The cost pool can be neither "Internal Labor" nor "External Labor".

I've written the formula like this, but it doesn't seem to work right.

=If(Lookup(Third Party Number,Vendor List,Supplier Number,Vendor Type)="VENDOR" AND Cost Pool NOT IN("Internal Labor","External Labor"),"Yes","No")

For entries that return a vendor type of something other than "VENDOR," this formula works, and the result is "No." However, if the vendor type is "VENDOR," and the cost pool is something other than "Internal Labor" or "External Labor," then the result is still "No."

I wonder if my "NOT" in front of "IN()" is the cause. Can I use negation like this within an If() conditional?

Hi Jonathan,

Would a nested IF() formula be any use here?  Something like this?

=If(Lookup(Third Party Number,Vendor List,Supplier Number,Vendor Type)="VENDOR", If(Lookup(Third Party Number,Vendor List,Supplier Number,Vendor Type)<>"Internal Labor",If(Lookup(Third Party Number,Vendor List,Supplier Number,Vendor Type)<>"External Labor"),"Yes","No")

Regards,

Phil#

Hi, Phil!

Yes, a nested IF() function would work in this case. I thought of that earlier, but it would end up being really, really long. I used this as an example, but my question really focuses on the general case of negating one of the conditional statements within the IF() function. With a NOT boolean, we could avoid two layers of that nested IF. I would be very surprised if negation doesn't exist in Apptio, but I think I'm just using it incorrectly in my example.

Hi Jonathan,

I was checking the NOT IN function and it is working fine.  I would suggest you to break down the formula:

1. Just include only the Lookup in the IF condition and do a check (yes/no) if the return value of the Lookup matches VENDOR

2. Only the NOT IN function in the IF condition and check if it is working properly ( I did this on my system and it works)

Regards

varghese

Hey, Varghese.

I think you're right. I broke it up, and it turns out that the lookup isn't functioning properly, because some of my lookup values have an invisible character in front of them. Trim() doesn't work on them, because it's not a space character. I'll need to figure out what's causing this character to appear and what character it is. Then My NOT boolean should work properly.

Thanks for the tip!

You're welcome !