Do 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.
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.
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.
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.
In above example, I have used the following formula to create a two-way look-up.
How Does this Formula Works?
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.
- Create a drop down list in cell B1 using the heading of your main data table.
- Enter the following formula in the cell A1 & drag to the last cell.
- Select your new data table & insert an area chart.
- Now, you can use you drop-down list to use your dynamic chart.
Download Sample File
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.