How to assign a value if another cell value meets criteria - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Kenny LiKenny Li 

How to assign a value if another cell value meets criteria

I need to build a formula field that references the SKU in 1 cell, and checks it against a whole range of SKUs, to return a value in a 2nd range of cells (in this case, the manufacturer).  I know how to build a clunky if statement that includes the ENTIRE range of SKUs, but not only is this clunky and tedious to build, it exceeds the 3,900 character limit in the formula field.  Is there a way to create the SKU database in Salesforce, and then write a simple "Lookup" formula that helps me return the right data?  Right now we have someone going in and updating these fields by hand, which is not sustainable or scalable.  Any help would be appreciated!

It needs to look something like this, in function:

[Find this SKU in range of SKUs], [Return the corresponding manufacturer]

Thanks!!
David PierDavid Pier
Hey Kenny,

You can indeed do this with a VLOOKUP function in your formula.
See: Operators and Functions

Hope that helps!

Best,

David
Duckyforce.com
Kenny LiKenny Li
Hi David, thanks for the link!  I wasn't aware vlookup was usable in Salesforce until now.  

I started building the formula through teh ADvanced formula builder for my custom field, and then noted that the data I need to reference is actually in another custom object.  Is it possible to access that data?  The other custom object is basically my statics product database.  Can I use vlookup to reference a different custom object?  I saw the coding in the link you provided, adn looked like it allowed you to specify a different custom object.  Would that be right?

(I realize I may not be using custom objects in the way they were intended.)
Kenny LiKenny Li
I'm also not aware if it means I need to set up a master-detail relationship first, or lookup status, or what.  This is all very new to me!
Anna HendryAnna Hendry
Hi Kenny, if you found this answer helpful, please come back and let us know by selecting 'Best Answer' underneath the comment. This will mark your question as resolved and will help the community in the future if they have a similar inquiry. Thanks! 
David PierDavid Pier
Hey Kenny,

There doesn't need to be any lookup or master-detail relationship.

The formula would probably look something like this:

VLOOKUP($ObjectType.Custom_Object__c.Fields.Manufacturer__c, $ObjectType.Custom_Object__c.Fields.SKU__c, SKU__c)

Does that make snese?

David
Duckyforce.com
Kenny LiKenny Li
Thanks guys, I'm going out the VLOOKUP function as you wrote it; I did try it once and couldn't get it to clear the syntax tho.  Just so I understand, the code "Objecttype" and "Fields" does need to be included in there, right?
David PierDavid Pier
Hey Kenny- they both need to be included! Want to paste the formula you're working with?
Kenny LiKenny Li
Hi there David,

I tried the formula and it still told me my fields don't exist.  This is what I wrote:

VLOOKUP($ObjectType.SKU_Factory__c.Fields.Factory__c,$ObjectType.SKU_Factory__c.Fields.Name,  SKU_no__c )

Could you help to troubleshoot?  I'd really appreciate it!!
David PierDavid Pier
Hey Kenny,

I am a fool. The VLOOKUP function is only available in validation rules.
This completely slipped my mind, and I apologize for leading you on a wild goose chase!

Please vote for this idea: Expanding the VLOOKUP for Formulas

That being said- the only way around this would be to write an Apex Trigger. You may be able to find some code samples on the developer boards...

If you make it to Dreamforce '13- I owe you a beer.

David