Sunday, November 22, 2009

Excel Formula : Using Vlookup Function

One Golden Rule while working with Excel Formula's is never enter a hard coded value into Formula. If you can calculate a value with a formula, it should not be hard coded into your formula's

Today we will talk about how this is relevant in using Vlookup Function. We all use Vlookup function in our day to day working with the data in Excel. Given below is the Syntex for Vlookup function just for reference purpose.

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

Picture on the above is typical data table used by us. Column C to N are hidden in this just to keep the table visible on screen. We need to get the sales value using vlookup formula. Most of the users (including me till some time back) hardcode col_index_num. Most common use of Vlookup Function for getting data from the table like above will be a formula like this.

Here lookup_value is given at Cell B13, table_array is a Range B3:P10, col_index_num is 15 as it is 15th Column starting from Column B, range_lookup is 0 as we are looking for exact match.

In above formula the col_index_num is hard coded. Formula will give correct results till you do not insert or delete columns in between. Once you have done so , all your formulas will not update sales figure but reference some other value.

However with the following formula you can overcome this issue. You can calculate the number of columns with the help of Columns function of Excel.

COLUMNS(B3:P3) will be calculated as 15 and in case you insert any column between table_array your formula will change to VLOOKUP(B13,$B$3:$Q$10,COLUMNS(B3:Q3),0). Now columns function withing your formula will be calculated as 16 giving you correct value. This makes your Vlookup function dynamic.

Do refer to my earlier tip on Dynamic Range Names to make table_array also dynamic.

You are welcome to share your way of entering Vlookup function by way of comments to this post.


  1. Never use VLOOKUP.
    Use Match/Index


    In your ex

    It has many advantages over VLOOKUPS..

  2. Hello Sam

    Wecome to my blog. Thanks for your suggestion.

    Regards//Yogesh Gupta

  3. Woderful content. I am regularly follow this blog. Thank you for updating such a good content. Please follow my Travel blog for more information about Eco Park in Kolkata.

    My other posts are:
    Victoria Memorial in Kolkata.
    Scuba Diving in Goa.
    Top visiting places in GOA.
    Calangute Beach in Goa.
    Candolim Beach in Goa.
    Baga Beach in Goa.

  4. Excel is one of the most powerful Microsoft tools, as it helps in managing and analyzing large data. When I was looking for an essay writing service uk based technology essay, I got an essay on excel and its capabilities. Then I got to know that it provides great help to different types of business to generate large analytical reports and do forecasting and calculation stuff. This shows the power and ability of this tool and its importance in organizational use.

  5. Using Vlookup Function in excel was always bothering me and that made it difficult for me to prepare my presentation. But now you have made the whole process very easy for me. Dissertation Proposal Writing Service

  6. You also share these Messages to wish the happiness of New Year to your family members and relatives also Happy New Year Glasses for 2022. This post is about Best New Year 2022 Messages, SMS for your friends and family or to someone who loves you and you love them, so we have compiled the list of Best New Year 2022 Messages for you, Check this out.

  7. We are an expert IOS Apps Development Services in Canada creating customized and responsive mobile apps for the best user experience.

  8. It is feasible to inquire "Take care of my task for me". Presently with the rise of the internet based Write My Essay For Me Cheap task composing office, one can pay somebody to finish tasks for their benefit. With regards to composing tasks, numerous understudies mishandle.

  9. I appreciate you writing such a unique blog article, and you are correct that there are instances Excel Formula : Using VLOOKUP Function We ought to consider that, in my opinion get urgent science assignment help If ever you need chemical engineering assignment help because they have the best writers for assignment writing I always use this site for my assignment help and that's why I will recommended you to use this site.


Learn how to create Excel dashboards.
Yogesh Gupta's Excel Tips