How to use VlookUp Function in Excel

1. How to use VLookUp in Excel?
2. Formula for VLookUp
3. Use of VLookUp in Excel
4. How does VLookUp work?
5. VLookUp in Financial Modeling and Financial Analysis
6. How to get started with Vlookup?
7. What can I do if I need to find data that is not vertically placed?

How to Use VLookUp in Excel?

VlookUp in Excel is one of the most significant functions in Excel, which has also been one of the most desired functions for those learning MS Excel. The function helps in looking up data in a table that is organized vertically. It supports approximate as well as precise matching and wildcards (* ?) for partial matches. Lookup values should appear in the initial column of the table that is passed in VLookUp, and once your value has been located, you may specify the column from which it can fetch the data.

Formula for VLookUp

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

If we translate it in easy language, we would say you can look for this piece of information in a certain area and provide me some corresponding data from some other column.

The following argument is used by the VLookUp function:

1. Lookup_value (required argument) – Lookup_value explains the exact value that we want to find in the initial column of a table.
2. Table_array (required argument) – The table array is the data array that has to be searched. The VLookUp function helps in searching in the extreme left column of the array.
3. Col_index_num (required argument) – This is basically an integer that specifies the column number of a supplied table_array, from which you would want to return a value.
4. Range_lookup (optional argument) – The Range_lookup specifies whether you are looking for an exact or partial match. This can be specified in terms of TRUE or FALSE argument where False would mean that you are looking for an exact match for the Lookup_value in the Table_array and True would mean that a partial match is also acceptable, in which case it picks up the partial match it finds.

Use of VLookUp in Excel

1. Organize the data – The foremost step to effectively use the VLookUp function is to ensure that your data is thoroughly organized as well as apt to use the function.
2. Reference different spreadsheets – Through the VLookUP function, you can create a reference point between two Excel Spreadsheets. This helps in saving manual efforts as well as reducing human errors when handling a large set of data.
3. Automate function – When clubbed with other Excel functionalities such as PIVOT table, the VLookUP can be a powerful tool in automating certain tasks so that the minute you update the value in the source data, it reflects on all the linked sheets.

How does VLookUp work?

Apart from knowing how to use VLookUp in Excel, you also need to have an idea of how the actual formula works. This will help in comprehending the innate logics of the function and troubleshooting even complex VLookUP functions.

VLookUp functions in a left to right manner; therefore, you need to make sure that the information you are looking for is on the left side of the corresponding data that you want to extract.

1. Tell the function what to lookup

You can tell Excel what to look for. You have to start it by typing the formula “=VLOOKUP(“. Now select the cell that has information that you want to look up. 

2. Tell the function where to look

You now have to select the table where the data is placed and command Excel to search in the extreme left column to find the information that you selected in the previous step.

3. Tell Excel what column to output the data from

In this step, you would need to command Excel about the column that contains the data that you want to have as an output from the VLookUp. To do it successfully, Excel needs a number that can match the column number in the table.

4. Precise or approximate match

This is the final step in which you have to command Excel that you are looking for a precise or approximate match. You need to enter either True or False in the formula.

VLookUp in Financial Modeling and Financial Analysis

VLookUp formulas are quite often used in financial modeling and many other types of financial analysis. This helps in making models more dynamic and implementing many scenarios. Especially in the world of finance, where financial analysts work on a large number of complex data sets, the VLookUp can help save a lot of time and effort. One of the important functions of finance is Financial Planning and Analysis, where the VLookup can be specifically used to compare different reports in common Excel formats and draw trends for the benefit of the business.

Read More:- Excel for Beginners

Things to remember about the VLookUp Function

1. When range-lookup is not there, the VLookUp function will enable a non-exact match; however, a precise match will be used if this is one.
2. The major limitation of this function is that it mostly looks right. It gets data from the columns to the right in the initial column in the table.
3. In case the lookup column has duplicate values, VLookUp will match only the first value.
4. The function is agnostic of the case of the character.
5. Taking as an example, there is a VLookUp formula existing in a worksheet. In such a situation, the formulas may break if a column is inserted in the table. This happens like that since the hard-coded column index values do not automatically change if you insert or delete the columns.
6. The use of wildcards is used by VLookUp. For example, an asterisk (*) or a question mark (?).
7. In another example, you are working with the function in a table that contains numbers that are entered as text. If only the numbers are retrieved as text from a column in the table, it will not really matter. However, if the first column of the table has numbers that are entered as text, you will get an #N/A! error in case the lookup value is not in the text form.
8. #N/A! error – It happens if the match to the supplied lookup_value is not found by the VLookUp function.
9. #REF! error – It happens if:

• The col_index_num argument is more than the column numbers in the supplied table_array; or
• The formula that has been attempted to reference cells that do not exist.

10. #VALUE! error – It happens if:

• The col_index_num contains a number less than 0 or in other words a Negative value
• The col_index_num contains one or more text characters
• Usually happens in case of a typo so it can be fixed easily by inspecting the VLookUp formula thoroughly

How to get started with Vlookup?

There are a total of four pieces of information that you will require to build the VLookUp syntax:

• The value that you want to look up is also known as the lookup value.
• Talking about the range where the lookup value is placed; you need to remember that the lookup value must be in the initial column in the range where VLookUp can work correctly.  Taking as an example, if the lookup value is cell C2, the range should start with C.
• There is a column number in the range that has a return value. Taking for example, to specify B2:D11 as the range, you have to count B as the initial column, the C would be the second, and so on.
• You can go ahead and specify TRUE in case you need an approximate match. You can specify FALSE if you want a precise match of the return value. In case nothing is specified, the default value will automatically be TRUE or an approximate match.

Now you can put all of the pieces together as follows:

=VLOOKUP(lookup value, range comprising the lookup value, the column number in the range comprising the return value, Approximate match (TRUE) or Exact match (FALSE)).

What can I do if I need to find data that is not vertically placed?

If you need to use an excel spreadsheet where the data has been placed horizontally instead of vertically, running a VLookUp formula will not be able to yield the right results. This is true since the VLookUp or Vertical LookUp only works on a Vertically placed data set. Here are the two things you can try:

• You can try to run a HLookUp which is a HoriZontal LookUp equivalent of the VLookUp formula. The HLookUp can be used in a similar way to the VLookUP is used to fetch and analyze data.
• You can transpose the data from Horizontal to Vertical fields. To transpose, copy the entire data that you want vertically placed, and then go to the destination cell where you want to place the data. Once there, do a right-click on the cell and choose “Paste Special” and then “Transpose Data”

These functions make VLookUp one of the most valuable tools that can be used in Excel. Once you know VLookUp, you can use it in a variety of scenarios and become known as an expert in Excel. To learn more about VLookUp and other useful Advanced Excel functions and formulas, you can head to Great Learning Academy, and develop your competence in this desirable field. Once you have a thorough understanding of Excel formulas, you can move to learn more advanced tools such as VBA.