Many times when you are looking up data, you know which column you want to pull from. It may be that column D always contains the January sales figures and that’s what you want, so you pull in column D from your Vlookup function.
However, what if you table changes? Maybe you pull in data from the web or from outside sources and those columns are never the same. But you always want to still pull in January sales figures. It’s just not always column D.
If you don’t know how to do this, I am about to show you!
We will use the following example to do this:
We have a chart of Average temperatures by month for different cities in the US.
We get this information from the internet and we pull it in each month. However the cities are never in the same column.
We want to pull in the March temperatures for Dallas. However, Dallas is not always column C and March is not always Row 5.
To do this, let’s start off with a simple Vlookup to pull this in. But let’s pretend that Dallas is always column C.
We would use the following formula:
What this does is it is looking up the month “March” in column A. Then it goes over to the 3rd column, which is C and pulls in the number there. That number is 64.
This is great, however what happens when we run this net month and Dallas is in column E?
Instead of using 3 for your lookup column in the formula above, we need to make that interactive. We do this by using the MATCH formula.
The MATCH formula will look for the word “Dallas” in a range you set and return a number telling what position it is.
So in our example, we could do the following MATCH formula:
What this does is look for the term “Dallas” in cells A2 to E2. Since this occurs in cell C, that is the 3rd column, so this formula returns a 3.
In those months where Dallas is in column E, this Match function would return a 5.
So now, we can replace the 3 in our VLOOKUP function with that MATCH function we just did.
Our new formula is:
See the bolded part above is just the MATCH function replacing the 3.
Now this VLOOKUP is interactive. It will look for the term Dallas and return that temperature instead of whatever city happens to be in column C.
I hope this was helpful. If you would like a lot more helpful videos and tutorials on Excel Functions, check out our program Master Excel Functions. It has 4.5 hours of videos and 114 page ebook helping you learn Excel through Formulas!
To check it out, go to http://excelbyjoe.com/excel-software-solutions/master-excel-functions/