Excel University Blog
Read on for in-depth articles, tutorials, and videos. Search or browse for specific topics. Be sure to subscribe if you'd like to be notified when we write something new.
VLOOKUP
There are numerous lookup functions in Excel, and often, their capabilities overlap. And, that is true with many things in Excel…there are often multiple ways to accomplish any given task. Performing lookups is no exception. So, how are we supposed to know which lookup function to use? Often, Excel users try to decide between VLOOKUP…
Read More
I recently received an Excel question about how to perform a specific list comparison, and I thought I’d demonstrate how to use a Get & Transform query as an alternative to the formula-based list comparisons typically performed with functions such as VLOOKUP and COUNTIFS. The original question is: “I am trying to use a list…
Read More
If you have ever wished that the VLOOKUP function could return the sum of two or more related columns, this trick will get you there. Objective Before we get into the details, let’s be clear about our objective. We have some transactions that were exported from our accounting system as shown below. We would like…
Read More
When you need to perform a lookup, your instinct tells you to use VLOOKUP. But, when your lookup uses multiple conditions and columns, you may be inclined to use SUMIFS. However, when the value you need to return is a text string, rather than a numeric value, you are precluded from using SUMIFS since it only…
Read More
In this post, we’ll perform a two-dimensional lookup with Excel’s VLOOKUP function. Objective Let’s begin by clarifying our objective and what is meant by the term two-dimensional lookup. We have stored our price list in a table, and the price for each item varies based on the region. This is illustrated in the screenshot below. To…
Read More
In this post, we’ll examine a couple of ideas for computing income tax in Excel using tax tables. Specifically, we’ll use VLOOKUP with a helper column, we’ll remove the helper column with SUMPRODUCT, and then we’ll use data validation and the INDIRECT function to make it easy to pick the desired tax table, such as single or married…
Read More
In this post, we’ll explore a method to allow a user to select an item from a drop-down list and then use formulas with the VLOOKUP function to retrieve multiple values from the related item table. Objective Before digging into the mechanics, let’s review our objective with an example. We would like to allow our…
Read More
Of all the functions introduced in Excel 2007, 2010, and 2013, my personal favorite is SUMIFS. The SUMIFS function performs multiple condition summing. The function is designed with AND logic, but, there are several techniques that allow us to use OR logic instead. This post explores a few of them. Note: if your version of…
Read More
In a previous post, we explored how to use the TRIM function to remove trailing spaces from lookup values used in a VLOOKUP function. In this post, we’ll tackle the reverse issue, where the values in the lookup column contain trailing spaces, by using wildcards in the VLOOKUP function. Note: depending on your version of…
Read More
I’m a huge fan of the VLOOKUP function, and am surprised by its day-to-day utility for accountants. In this post, we use the VLOOKUP function to convert or translate calendar year transaction dates into fiscal year periods, such as a fiscal quarter. To accomplish this, we’ll first need to investigate in detail the function’s fourth…
Read More