For all of us excel lovers out there, we all know that Vlookup formulas are definitely one of the most common functions in excel. Among other things, this function helps us with data analysis, summaries and key reporting elements.
We all know that even though the formula is not the hardest to use, Excel users all over the word spend a considerable amount of time perfecting the formula and making sure all the technical aspects of it are working properly.
So why don’t you save yourself the trouble and just automate Vlookup in Excel?
Looking at the example above us, assuming we would like to pull March revenue for customer #1002, we would use the Vlookup formula and retrieve column 4 to get the revenue for March.
Now let’s say we wanted to get the revenue for the same customer in May we would have to do it again and manually input row number 5 instead of 4. Leaving us to do time consuming, manual work and more prone to errors.
How do we solve this problem?
The Match function is the ultimate solution to use with the vlookup function in order to overcome the issues mentioned above.
What you will need to do is substitute the 4 with the Match formula.
- Type in MATCH( instead of the column index.
- Select the value you are looking up – in this case it is B11: Mar
- Select the lookup array in this case the row of our table: A-G
- End the formula by adding a comma, a zero and a closed parenthesis.
Now for whichever month you change your lookup value to it will automatically bring back the revenue for that month without changing the formula.
Conclusion
It is so easy to waste time because we don’t know what other options are out there. Get started now on saving your time and producing more efficient results.
Have you ever considered how companies manage their finances?
There are a lot of companies out there that take care of these problems. The one thing that we as users are lacking, is that we work in Excel and that isn’t going to change any time soon. Which is why we need products that solve this problem without changing the way we do things.
Datarails’ FP&A team has created a software that automatically consolidates all your spreadsheets to allow for more granular data analysis, a live feed of data & analysis with monthly reporting. What distinguishes this software compared to others is that it integrates with Excel, meaning nothing you do changes.
It just solves all of the pain points you might be experiencing when working with excel like the one discussed in this blog.
