Quick Excel Tips: Dynamic Lookup

Today, I’m going to show you one of my favorite Excel tips: Dynamic Lookup or Dynamic Searching by using a combination of VLOOKUP and MATCH functions. Bascially, this function combo makes it so that the column that you pull the data from is dynamic based on the header making it more flexible than VLOOKUP by itself because you don’t have to rely on knowing the index column number.

In my example (that you can download below), in cell B2 I have this formula that combines VLOOKUP and MATCH:

=VLOOKUP($A2, $D$2:$G$14, MATCH($B$1,$D$1:$G$1,0),FALSE)

The column header in B matches one of the column headers in D, E, F, G - it doesn’t matter which one, you can change it and the values update automatically, which is the beauty of this formula. This makes it easy to add or remove columns without having to update your formulas. Not only is it more dynamic, the index column need not be on the left. Try it yourself!

Download my Dynamic Lookup formula example spreadsheet here.

Personal Finance Tracking Template

I think I've talked about posting this before (and never did) but after a reader’s request I've finally made my personal finance tracking spreadsheet available to download. Since becoming a new parent it’s even more important than ever for me to track my finances due to all the additional expenses, like doctor visits, buying diapers, more groceries, daycare (holy crap it’s expensive!!) and more. You don’t need to buy any expensive personal finance software, just use a simple Excel spreadsheet and I'll show you how.

The first sheet in the workbook is the Categories page where I list what I want to categorize each of my bills as: gas, electric, mortgage, cable, etc.

Then there is an Income page and an Expenses page with drop down lists driven by what is entered on the Categories page. Finally, a Totals page shows you how much you’re bringing in and how much you’re spending each month. I left a few rows filled in on the template so you can easily see how it works (data is made up and not real).

Pie charts are a great way to visualize your monthly expenses to see what you’re spending the most money on. Sometimes it's quite eye opening, like "wow, we spend a LOT of money eating out! Maybe we should start to make more home cooked meals."

 

I use my spreadsheet to see how much we’re spending on food, gas, entertainment, etc. and you can use it as a free budget planner tool too! You can set a montly goal on the Totals page and see if your spending stays below it.

Personal Finance Tracking Spreadsheet.xls download

This template uses:

• Data Validation
• Name Manager
• Pie charts
• Line Graphs
• =Month()
• =Average()
• =Max()
• =Min()
• =Sumif()

 

As you can see on the example income page, I'm a big believer that in this day and age everyone should try to have more than one source of income, even if your secondary income is much smaller than your primary income stream.

 

This can be accomplished by anyone who has access to the internet, because there are multiple ways to make a little extra in your spare time: online survey taker, freelancer, writer, affiliate marketer, social media manager, graphic designer, Fiverr gigs, blogging, Amazon buyback program, etc. All it takes is a little extra effort.