5 Craziest Microsoft Excel VLOOKUP Tricks You Must Know

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.

1.Simple Use Of Lookup (Let’s start with simple one first to revise)

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.

2.Multi Value Vlookup Trick

What if we have been asked to fetch Employee salary and other employee details by using employee id?
Formula: =VLOOKUP(G2,A2:D6,{2,3,4},FALSE)

We succeeded in getting Employee name, Department and Salary only with employee ID. Still easy right?

3.Table Naming Trick

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?

4. Customer Error Trick

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.

5.Two-Dimensional Lookup Trick

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.

1.Watch the Phantom Extra Space – Vlookups don’t work if the Lookup Value has unwanted extra spaces. Use the Trim function to get rid of any extra spaces in the cell and then apply the VLookup. =TRIM(Cell Reference)
2.Search Column is the First Column- The VLOOKUP formula only looks in the first column of the select data
3.Not freezing the Table Array Range – The VLOOKUP freezes the range automatically if you are applying it to another workbook, but if working on the same sheet, you have to do it manually using the F4 Key
4.Leaving the Range Lookup Blank – By default excel applies an approximate match (TRUE), make sure if you applying an exact match you specify FALSE as an input