We all know that VLookup is one of the best feature or formulae that Excel has provided in order to make reporting and preparing smart dashboards easier.
VLOOKUP is one of the lookup or reference function used to find things in a table or a range by row.
There are four pieces of information that you will need in order to build the VLOOKUP syntax:
1.The value you want to look up, also called the lookup value.
2.The range where the lookup value is located. Remember that the lookup value should always be in the first column in the range for VLOOKUP to work correctly. For example, if your lookup value is in cell C2 then your range should start with C.
3.The column number in the range that contains the return value. For example, if you specify B2: D11 as the range, you should count B as the first column, C as the second, and so on.
4.Optionally, you can specify TRUE if you want an approximate match or FALSE if you want an exact match of the return value. If you don’t specify anything, the default value will always be the TRUE or approximate match.
Consider a table with basic employee details and we have been asked to find the employee name from employee ID.
In this, we were able to get employee name Robert using his Employee ID 12. It was the easy one, isn’t it? Let’s go little deeper.
What if we have been asked to fetch Employee salary and other employee details by using employee id?
We succeeded in getting Employee name, Department and Salary only with employee ID. Still easy right?
This is one of the cool tricks where you can name your table and use it with its name instead of range every time and also it’s good to use when you are using multiple tables to generate dashboards.
Below are the steps for naming the table:
Select the table range which you want to use and go to the left top corner to name it.
We selected range A1:E6 and named this table “Employee” so that next time we can use this range with some meaningful word.
Now, how to use?
Let’s go back to the previous example to find the salary from employee id.
Formula applied on column I2 and its =VLOOKUP (H2, Employee, 5, FALSE)
If you noticed, we used “Employee” instead of table range. Looks good right?
Showing customer error in case of required record is missing in the master table. In some of the cases, we need to show customer error for better and clear output. Please are the steps to achieve this.Formula Used: =IFERROR (VLOOKUP (H3, Employee, 5, FALSE),”Not Found”)
You can see that instead of showing default meaningless error it is showing custom error “Not Found” for missing records. we don’t have employee details for the employee ID 40.
Under usual circumstances, while using VLOOKUP we hard coded the column number from which Excel should get the result. Consider the following formula:=VLOOKUP(G5,B2:C10,2)
“2” is actually column number that we mentioned. But we may have situations where our data is spread in different columns, and we need to get the right column first based on criteria and then we perform the lookup. This is called 2D lookup or two-dimensional or 2 criteria lookup. It goes with many names.
Consider below scenario where price needs to be selected based on product + size.
Make sure to check below to avoid error while practicing.