Excel Productivity Guide

Get instant access to this free e-book fully loaded with Useful Excel Tips, 80+ Keyboard Shortcuts, and VBA Codes.

How To Use Vlookup and Match as a Combination

    guide to learn about vlookup and match functionDo you know Vlookup and Match is a deadly combination?

    You can use the combination of vlookup and match to create data for your dynamic charting.

    In this post, I’ll reveal the secret about how I use VLOOKUP And MATCH to create data for my dynamic charts with no efforts.

    I’ll also tell you about reasons to use VLOOKUP And MATCH to increase the power of VLOOKUP. But before doing this let me explain you everything about the combination of these two awesome functions.

    Combination of VLOOKUP And MATCH

    VLOOKUP Function is no doubt the most popular & useful function of the Excel Function Library. But unfortunately, there are some problems with it. One of them is that VLOOKUP can’t work like dynamic function.

    Problem With VLOOKUP: In below table, I am using VLOOKUP to the get the value from the table where I have month wise & zone wise data. Now, if you look carefully the major problem here is to get the values from different columns I have to manually change the col_index_number.

    Reason Of Combining Vlookup And Match

    If I want value from the north column I have to use 2 or If I want to get value from the south column I have to use 5. So all I am doing here is manual.

    What Match Function can do?

    After understanding above example one thing is clear that we need to automate the col_index_number in VLOOKUP to get dynamic results. And, MATCH Function is here to rescue.

    How Does Match Function Works?

    If you have ever used match function in your work you know that match function can lookup for a value from a list of values & return the number representing the position of the lookup value in that list.

    Let’s continue with above example.

    Match in the combination of VLOOKUP And MATCH

    In above example, I have taken headings of the table & I have used match function to get the position of each heading in from the range by using following formula.

    =MATCH(A2,A4:F4,0)

    When I am using “West” for lookup value, the formula is returning 4 because the west is on 4th position in the list.

    Combine Working of VLOOKUP And MATCH

    Let’s combine the VLOOKUP and MATCH now. So, that we can get into the world of dynamic data.

    Two Way Look Up Using VLOOKUP And MATCH

    In above example, I have used the following formula to create a two-way look-up.

    =VLOOKUP(A2,A4:F16,MATCH(B2,A4:F4,0),0)

    How Does this Formula Works?

    Using VLOOKUP With MATCH Formula

    In above example, I am using MATCH function instead of a constant col_index_number in VLOOKUP. And, I have specified two different values to lookup one is with VLOOKUP & the other is with MATCH.

    Now the idea is when I use “East” as a lookup value for MATCH it returns 3 which is the position of “East” in the range A4:F4. VLOOKUP use 3 as a col_index_number & return 174.

    Using VLOOKUP and MATCH is simple & easy. It also allows us for better control over VLOOKUP especially when you are working with data tables.

    Put All Together To Create A Dynamic Data For a Chart

    If you check, my all post related to dynamic charts I always use VLOOKUP And MATCH to create a dynamic table.

    So Let’s Create a Dynamic area chart by using data from above example. And, here are the steps to create it.

    • First of all, create a two-column table along with your main table.

    Data Table With VLOOKUP AND MATCH

    • Create a drop down list in cell B1 using the heading of your main data table.

    Create Drop Down List To Use VLOOKUP AND MATCH

    • Enter the following formula in the cell A1 & drag to the last cell.

    =VLOOKUP(A2,$D$2:$I$13,MATCH($B$1,$D$1:$I$1,0),0)

    Formula With VLOOKUP AND MATCH To Create A Dynamic Table

    • Select your new data table & insert an area chart.
    • Now, you can use you drop-down list to use your dynamic chart.

    Dynamic Chart With VLOOKUP AND MATCH

    Download Sample File

    download sample excel file to learn about vlookup and match

    Last Words

    So this was the whole story of VLOOKUP And MATCH which leads you to the mastery of VLOOKUP. Using this combination of VLOOKUP And MATCH will give some super power to VLOOKUP & you can perform a two-way lookup.

    And, the way it help us to make data for dynamic charts is amazing.

    Over To You

    So what’s up at your side.

    Have you ever used the combination of VLOOKUP And MATCH before in excel?

    Is there any other way we can use both of these function?

    Please share with me in the comment box.

    Some Amazing Tips On Excel Formula



    • William Kiarie

      This post is written in a very easy to understand format. It is a great approach especially when you have to insert/delete columns in your lookup table.

      • Puneet Gogia

        Thanks for your words. William.

    • Manali

      These Tips and Explanations are simply Excellent and am Elated to join abd subscribe and love downloading the materials. Thankyou so much.
      Time to drive and upgrade my Excel Skills.
      😊
      With Regards,
      Manali

      • Puneet Gogia

        I’m so glad you liked it.

    • Tim Frielingsdorf

      Where do you see the advantage of VLOOKUP and MATCH versus INDEX and MATCH?