# Tag Archives: VLOOKUP

## Battle of Heavyweights: Round 1

It is time! In the first post, we met VLOOKUP. In the second post, we met SUMIFS. And now, the battle begins! Here is how it will work. The competition will consist of several rounds. In each round, we will use VLOOKUP and then SUMIFS to perform the same task. Then, we’ll determine who wins […]

## Battle of Heavyweights: SUMIFS

This is the second post in the Battle of Heavyweights series, where we are comparing VLOOKUP to SUMIFS in order to understand their key differences. Knowing these key differences will help us determine which to use in a given workbook. In the previous post, we were introduced to VLOOKUP. Now, it is time to meet […]

## Battle of Heavyweights: VLOOKUP

This is the first post in a series called Battle of Heavyweights. In the series, we’ll use a boxing metaphor to compare VLOOKUP and SUMIFS. Now, as you read the next paragraph, use your best announcer’s voice and be sure to imagine the roar of a cheering crowd 🙂 “Ladies and gentlemen, welcome to the […]

## VLOOKUP on Multiple Columns and Return Text

You want to perform a lookup with VLOOKUP, but, there are multiple lookup columns. So, what are you supposed to do? Combine them into a single lookup column? That is certainly one option, but, as with just about anything in Excel, there are multiple ways. In a previous post, I showed one way to do […]

## How to Build a PivotTable with the Data Model

Traditional PivotTables are an incredible feature of Excel, but, they are not without limits. Many of the typical restrictions are removed when you use the data model rather than a single Excel table. If you’d like to learn how to build a PivotTable using the data model, and learn what the data model is, strap […]

## VLOOKUP Hack #10: Maslow’s Hammer

Psychologist Abraham Maslow wrote a line that is often referred to as Maslow’s Hammer. It goes something like this: “I suppose it is tempting, if the only tool you have is a hammer, to treat everything as if it were a nail.” I’ve heard the simplified version more often: “If all you have is a […]

## VLOOKUP Hack #9: Partial Match

Let’s say we want VLOOKUP to match the lookup value “North Region” with “North Region Subtotal” stored in the lookup range. We started this series by looking at the 4th argument. We know it can be TRUE or FALSE. FALSE means exact match and TRUE means approximate match. So, what exactly is an approximate match? […]

## VLOOKUP Hack #8: Extra Spaces

Here’s a VLOOKUP question for you: What happens when the lookup value includes extra spaces? For example, a few trailing spaces, or leading spaces, or, extra spaces in the middle of the text string. When the lookup value has extra spaces, but the lookup range values don’t, VLOOKUP runs into problems. This leads us to […]

## VLOOKUP Hack #7: Different Columns

In this post, we’ll continue hacking the 1st argument. Here is the basic setup. We are building a report. The report structure uses multiple columns to store the report labels, such as sales, cost of sales, selling expenses, and so on. So, can we write a formula that works when the lookup values are stored […]

## VLOOKUP Hack #6: Different Data Types

We started this blog series by examining the 4th argument. We’ve since hacked the 3rd argument and then the 2nd argument. Now, as you can imagine, it is time to hack the 1st argument. There are many fun hacks we can do with the 1st argument, so, I’ll cover them over a few posts. In […]