With an office full of accounting professionals that spend more time in Excel than we’d like to admit to ourselves, we’ve racked up quite the toolbox of best practices in the program. We’re excited to share this wealth of knowledge with you. Because Excel isn’t simply a tool for accountants, we hope you’ll find these hacks helpful within your own industries, roles and workplaces.
Last month, our team members, Travis Aguirre and Lee Gyomlai, shared hacks surrounding “Center Across Selection” and “Go To Special.” Take a look here.
This month, based on common questions we’ve received from our clients lately, we bring you “Subtracting Dates” from Lee and “IndexMatch” from Travis.
SUBTRACTING DATES
Purpose:
Have you ever been frustrated by Excel’s solution to subtracting dates, by only providing you with the number of days? This tool allows you to know the exact number of days, months or years between two dates, instead of simply days.
Why use it?
Subtracting dates is an excellent Excel tool to calculate depreciation. More so, this tool allows you to do so efficiently and accurately.
Limitations:
While subtracting dates doesn’t have any major limitations, you may occasionally run into slight rounding differences with special circumstances like leap years.
Steps to subtract dates:
- For days: Excel does this automatically. Just take the day that appears in the future and subtract it from the date that appears in the past, like so:
Here, the value, 2921, is the result, which is the number of days between values A and B.
- For months: This requires more work. Use the formula below.
=ROUND(((YEAR(A12)-YEAR(B12))*12)+(MONTH(A12)-MONTH(B12)),0)
This formula figures out the number of years, multiplies them by 12 and then takes the remainder of the months and adds the two results.
- For years: Just use the following formula.
=ROUND((YEAR(A12)-YEAR(B12),0)
And voila!
INDEXMATCH
Purpose:
While “vlookup” (or vertical lookup) is the most commonly used lookup tool, once you’ve mastered it, the “index()” and “match()” functions can be used, in conjunction, to create an even more powerful lookup tool. For instance, if you’re interested in looking up certain data, such as produce prices, within a larger spreadsheet that includes prices for all food your business sells, IndexMatch allows you to dictate what you want to look up and where (which column) you will find the price.
Why use it?
Especially when compared to “vlookup,” IndexMatch provides the following benefits:
- Increased flexibility allows you to match against rows and/or columns.
- Increased accuracy when adding and removing columns from a referenced table or range due to the dynamic lookup range, especially when modifications are made.
- Increased ease of use because lookup value can be to the left or right (above or below if looking up horizontally) of the result column.
Overall, IndexMatch is more dynamic than the commonly-used “vlookup” as it will update itself as you remove or add columns and/or rows within your Excel worksheet.
Limitations:
The two-step process involved with “index()” and “match()” can be more difficult to master when compared to vlookup or hlookup.
Understanding the functions:
Index()
The “index” function is used to return a value from with a table or range.
Syntax: =index(array,row_num,column_num)
Match()
The “match” function searches for a specified item in a range of cells, and then returns the relative position of that item in the range.
Syntax: =match(lookup_value,lookup_array,match_type)
The following shows similarities between the familiar vlookup and the not so familiar IndexMatch. The values highlighted in the same color are essentially asking for the same information.
=vlookup(lookup_value, table_array, col_index_num, range_lookup)
=index(array, match(lookup_value, lookup_array, match_type)
Examples:
See how easy it is? Time to see how IndexMatch will help you!
Be sure to follow Weinstein Spira on Facebook for new Excel hacks each month and share the knowledge with your accounting industry friends and peers.
