# Tag Archives: VLOOKUP

## VLOOKUP vs INDEX-MATCH

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 […]

## Get & Transform: An Alternative to VLOOKUP List Comparisons

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 […]

## Return the Sum of Two or More Columns with VLOOKUP

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 […]

## Multi-Column Lookup with VLOOKUP and SUMIFS

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 […]

## Two-Dimensional VLOOKUP

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 […]

## Income Tax Formula

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 […]

## Select Drop Down Item and use VLOOKUP to Return Multiple Attributes

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 […]

## SUMIFS with OR

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. Objective Let’s be clear about […]

## VLOOKUP and Wildcards

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. Objective Before we get started, let’s […]

## Create Fiscal Year Periods with VLOOKUP

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 […]