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. Recently I came across some of the cool and craziest features of VLookup which I would like to share with you guys and I’m sure you all love it. Let’s get started before you get bored with the introduction.
VLOOKUP is one of the lookup or reference function used to find things in a table or a range by row. Before I show the lookup tricks, I’m assuming you already know some of the basic features of the same and just to revise the formula format see below,


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.

Now put all of the above together as follows:

=VLOOKUP(lookup value, the range containing the lookup value, the column number in the range containing the return value, optionally specify TRUE for approximate match or FALSE for an exact match).


1.Simple Use Of Lookup (Let’s start with the 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 a 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 the employee id.



The 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 the 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.

Note: The naming table has been used instead of range If you don’t recall then go back to trick 3.


5.Two-Dimensional Lookup Trick

Under usual circumstances, while using VLOOKUP we hardcoded 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 the price needs to be selected based on product + size.

Let’s understand the vlookup formula:

=VLOOKUP(H8,A8:D12,MATCH(H9,$A$8:$D$8,0),FALSE)

Here, VLOOKUP first checks the value in column H8 and search the value in the column mentioned in H9. In the above example, lookup searched for shirt price in column M.This was cool, isn’t it?


Make sure to check below to avoid error while practicing.
  • 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)

  • Search Column is the First Column- The VLOOKUP formula only looks in the first column of the select data

  • 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

  • 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

  • =VLOOKUP(lookup_value,table_array,col_index_num) – this will automatically pick up TRUE or approximate match

  • =VLOOKUP(lookup_value,table_array,col_index_num,0) – this also means FALSE

  • =VLOOKUP(lookup_value,table_array,col_index_num,) – if you don't write anything after the comma, this also means FALSE


Excel experts may find these tricks easy but it's good and makes the difference while preparing various reports and dashboards. These tricks are easy to understand and I believe every individual dealing with excel file should know. I hope you learned something new in this article.


If you have any question or feedback, feel free to write in the comment section of this article. I would be more than happy to see your comments.

7 Comments

  1. Thank you for the tips, Today I have learned something new:)

    ReplyDelete
  2. I enjoyed this article because it brought back some great spreadsheets in my past. On any job descriptions with Excel required, you also see the VLOOKUP as an example of Excel knowledge. These tricks, summarized by Buzz Analysis, give a great understanding of the functions abilities and especially error messages.

    However, If ever you want to step up those equations, may I suggest the following additions. Note: Enhancing equations in Excel can feel like building on a recipe at a 5 star restaurant...as least for Excel lovers like me.

    Let's start with our basic VLOOKUP, add an equal portion of HLOOKUP, (for a horizontal data search; the V in vlookup is for vertical). Infuse the two flavors with an INDEX function, some more commas and parenthesis.

    Reality example here:
    You have a chart of Store Sales by month, with "Sales by Months" across the top of the table and "Store Locations" down the left side. You can use and INDEX to find a specific stores sales for a specific month.

    = INDEX(VLOOKUP(what,where,count-across,false),HLOOKUP(what,where,count-down,false)

    Back to the recipe...Next, slowly add a the MATCH function, noted above in trick #5, within both the HLOOKUP and VLOOKUP functions. If you are "error intolerant", add trick #4, ISERROR function, to the equation. Lastly for those with a distinct pallet, you can substitute, trick #3 for any data range.

    Obviously, I enjoy this stuff to much. But what an Excel power rush! Yum

    ReplyDelete
  3. Thanks Donna Chamberlin for sharing..

    ReplyDelete
  4. so much fantastic info on here, : D.

    ReplyDelete
  5. Good day! Do you use Twitter? I'd like to follow you if that would be okay. I'm undoubtedly enjoying your blog and look forward to new posts.

    ReplyDelete
  6. Follow me at https://twitter.com/Buzz_Analysis

    ReplyDelete

Post a Comment

Previous Post Next Post