347 647 9001+1 714 797 8196Request a Call
Call Me

Excel Tips: How to use VLOOKUP in Excel

February 18, 2015
, , , , , , , ,

When someone asks me about my favorite function in Excel, my instant answer is VLOOKUP. When you use the VLOOKUP formula, you’ll always feel that you are performing some kind of magic trick. Yes, it is that awesome.

How to use VLOOKUP Function

The VLOOKUP() function has the following syntax:

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

You can learn using the Vlookup function by following these simple steps:

1.  As you can see in the image, the list of names also appears in the other part of the sheet (column G) and in our example we want to know the location of E in column H.

Using VLOOKUP function in Excel

2.  The first thing that the formula asks is lookup_value. It is generally the thing that you want to look up in your data. It can either be a value or a cell reference. In our case, we want to know the location of E and therefore we shall input its cell reference that is G2.

Filling lookup_value in VLOOKUP

3.  The next thing that the formula asks is table_array. Table_array is the place where our value resides. In our case the value is somewhere between A1to B6 so that becomes our table_array.

What is table_array in VLOOKUP formula?

4.  The next thing is adding the col_index_num. It is the column number where our value is situated. In our case we have selected two columns that is A and B. So Column A shall become 1 and Column B shall become column 2. Since the locations are mentioned in column 2 (column B), we shall input 2 as col_index_num.

what is col_index_num in VLOOKUP formula?

5.  The next in the formula is range_lookup. This determines that whether you are looking for an exact match (False) or an approximate one (true). This is optional and in our case we shall use False as we are searching for an exact value.

Using range_lookup in VLOOKUP formula

6.  Press enter and you shall get your answer.

How to use VLOOKUP function in Excel

7.  Instead of using the formula again and again to get the results in column H, you can easily drag or double click on cell’s auto fill handle. But while doing this you should lock the cells of table array otherwise this function may not work.

How to lock cells in Excel

8.  Now you can drag or double click the auto fill handle and you shall notice that all the values have been filled.

Using auto fill handle in VLOOKUP

If you have any doubts or questions then the comment box is right below, so shoot your queries and we shall reply as soon as possible.


About the Author

Trusted by Fortune 500 Companies and 10,000 Students from 40+ countries across the globe, it is one of the leading International Training providers for Finance Certifications like FRM®, CFA®, PRM®, Business Analytics, HR Analytics, Financial Modeling, and Operational Risk Modeling. EduPristine has conducted more than 500,000 man-hours of quality training in finance.


Global Association of Risk Professionals, Inc. (GARP®) does not endorse, promote, review or warrant the accuracy of the products or services offered by EduPristine for FRM® related information, nor does it endorse any pass rates claimed by the provider. Further, GARP® is not responsible for any fees or costs paid by the user to EduPristine nor is GARP® responsible for any fees or costs of any person or entity providing any services to EduPristine Study Program. FRM®, GARP® and Global Association of Risk Professionals®, are trademarks owned by the Global Association of Risk Professionals, Inc

CFA Institute does not endorse, promote, or warrant the accuracy or quality of the products or services offered by EduPristine. CFA Institute, CFA®, Claritas® and Chartered Financial Analyst® are trademarks owned by CFA Institute.

Utmost care has been taken to ensure that there is no copyright violation or infringement in any of our content. Still, in case you feel that there is any copyright violation of any kind please send a mail to and we will rectify it.

Popular Blogs: Whatsapp Revenue Model | CFA vs CPA | CMA vs CPA | ACCA vs CPA | CFA vs FRM

Post ID = 71459