VLOOKUP is very handy When you have a particular data in hand, and you need to find something in relation to it, it becomes nearly impossible to look after it by going through each and every data entry. Sometimes, not everything can be brought out by the search feature in the excel.

Conditional Formatting in Excel (Step-by-Step Tutorial)

Here’s an example:

vlookup with multiple criteria

As you can see in the image above, Employee ID, Department the employee is working in, the Project he or she is working under, and the Employer’s ID or in other words (Head ID).

Now, as you can see on the RHS (Right Hand Side), we need to look up for the Employee ID 23453’s Department.


Step 1: Input the formula =VLOOKUP(

So, to find the same, we will use the VLOOKUP Function which is as follows:


Step 2: Input the required values in the formula

The following formula requires a lookup value, table array, column index number and range lookup (which can be optional at times).

vlookup with multiple criteria

So, the first thing in the VLOOKUP Formula is the lookup value, which is the Employee ID 23453 (cell G4).

vlookup with multiple criteria

Then we will come to the point of table array which is basically the selection of the whole table which holds all the data in the excel sheet which is A1:D8.

vlookup with multiple criteria

Then we will come to the column index number. As we need to find the Department, the Employee holding the ID 23453 is in, we will write 2, because the 2nd column in the table array is the Department range.

vlookup with multiple criteria

Then we will move to the point of range lookup, which is optional at times. It has basically two options which are as follows:

(…)TRUE – Approximate match

(…)FALSE – Exact match

As we have inputted all the values that are exact and we need the Exact match as well, we will go with the FALSE option.

vlookup with multiple criteria

Step 3: Cross- check the formula and proceed

After inputting the range lookup, we will close the formula. Till now, the overall formula becomes as follows:


vlookup with multiple criteria

Now all we have to do is press enter and BAAAAMMMM!!! The answer is HR. The employee holding the ID number 23453 belongs to the HR Department.

vlookup with multiple criteria
vlookup with multiple criteria

So, it’s easy, isn’t it?