Everything You Need to Know About VLOOKUP

We all are familiar with excel, even if we do not know a lot about it.

18:21 10 July 2019

Most people have still used it at least once, well excel is a very handy software and is used to perform many different operations. Though it has many unique features, however, Excel VLOOKUP tops the list. If you want to extract information from a table then what you need is excel VLOOKUP. If you want to learn more about it then this is the right article for you.

The excel VLOOKUP feature is extremely simple and easy to use, however, there are so many things that could go wrong with it. The reason for that is not that you are using it wrong, it is just that the excel's VLOOKUP feature has an inherent flaw. It is automatically assumed that the users are okay with an approximate value, which is usually not the case.

The results might look normal at first but when you get into technicalities of it, they are nowhere near what you were looking for, and in the workplace where you actually need accuracy, it is a cause for big problems. Excel VLOOKUP requires using certain tips and hacks so that you can master the feature and avoid any problems in your work.

The first thing you need to know is that the V in VLOOKUP mean vertical, that means that you can look up information that is arranged vertically, but does not worry, if you want to extract data that is arranged horizontally then there is a feature for that as well, it is called the excel HLOOKUP, but that is an entirely different story.

To use VLOOKUP efficiently you need to ensure that your table is structured in a way which has all the data arranged vertically and also has a column at the left side which you can use to match all the rows. Once you have a column at the left for matching data you can very easily look up the values in the column that is at the right. Each column is assigned a column index which helps you look up values in retrieving a particular value.

While VLOOKUP is best for extracting the desired data it has certain limitations as well. The biggest one being that excel VLOOKUP can only look to the right for extracting data. Though, it does not pose a problem when all the data to be retrieved is already in the right column, but when any data is in the left column, this limitation can make thing so very wrong. Also, to take maximum benefit from VLOOKUP you need to supply small tables to it for looking up any kind of data or information. Since every problem or limitation has a solution, there is one for this as well, instead of using VLOOKUP, you can also opt for using INDEX and MATCH.

One thing that should be kept in mind while using VLOOKUP is that when there are two values or titles that are same then you should know that it will look up only the first value. Also, VLOOKUP is not case sensitive, so even if the values differ with upper and lower case, they both mean the same thing and VLOOKUP will give you the first value only.

There are two basic modes that you can use in excel VLOOKUP, these are exact and approximate modes. It does not take much to guess what these modes are for, as the name implies, the exact mode is used to give you specific data and information, while approximate mode gives you values that are close enough. Most people prefer using the exact mode, however, there are plenty of benefits of using the approximate mode as well. For instance, if you plan on making comparisons, or are looking for values that give you the best possible match, in that case using the approximate mode is the better choice.

But keep in mind that the VLOOKUP feature works on the approximate mode by default, if you want to use the exact mode you have to change it first. To change it to the exact mode you have to provide the correct 4th argument. By default it is written, 'true' in the 4th argument, so if you plan to use the exact mode make sure that you change the 4th argument to 'false'.

If there is more than one table from which you need to extract data or look up information then by using VLOOKUP you can very easily merge the desired cells and then look up the things that you want. This feature comes in handy when you have to make an analysis of some sorts. This makes it much easier to classify all the date in one place.