VLOOKUP explanation along with example

Suppose that you are creating Employee(a custom object) records in the SF org. Now since Employees are very important records, you do not want the users to create duplicate Employee again and again. For example, say one User already created an Employee Record with name - Mallareddy. Now, an another User who does not know about this tries to create an another Employee record with the same name - Mallareddy.and hence there is a potential duplication. This should not be allowed.

So you have two options now:

1. Add a Validation Rule on the Employee which checks whether there are any existing Employee records with the same name if so stop the User from saving it. Here comes the role of VLOOKUP.
2. Create an Apex Trigger(before Insert, before Update) which runs a SOQL query to get all the Employee records in the Org with same name as of the one thats getting created now. If the number of records is 1 then stop the User from creating the Employee using an addError method. This will require considerable development effort.

Now, since we are interested in VLOOKUP lets see as of how we can do the same. Create a Validation Rule as below:

(4) Name = VLOOKUP(

(1) $ObjectType.Employee__c.Fields.Name ,
(2) $ObjectType.Employee__c.Fields.Name ,
(3) Name)

Now, our basic requirement is to check if an another Employee with the same Name exists or not. So its obvious we are looking into the Name field. The logic is quite simple compare the Name (4) on the Current Record and the Name on that Employee record that matched with the same Name as of the one that we are creating now. Hence we use the equality operator (=).

Lets look into the VLOOKUP side. You could express the VLOOKUP formula as below: "Look into the Name(2) field on all the Employee Records and return value in it's Name (1) field if the it happens to be same as theName (3) on the Current Record."

The SFDC Document define VLOOKUP as follows:

VLOOKUP(field_to_return, field_on_lookup_object, lookup_value)

Searches an object for a record where the specified field matches the specified lookup_value. If a match is found, returns another specified field value.

Points to Remember:

1. VLOOKUP only available on Custom Objects. Vote for this idea: https://success.salesforce.com/ideaView?id=08730000000BqPs
2. VLOOKUP only available in Validation Rules.
3. VLOOKUP can only be done on the Name fields.
4. The field_to_return must be an auto number, roll-up summary, lookup relationship, master-detail relationship, checkbox, date, date/time, email, number, percent, phone, picklist, text, text area, orURL field type.
5. The field_on_lookup_object must be the Record Name field on a custom object.
6. The field_on_lookup_object and lookup_value must be the same data type.

Hope this helps!